跨用户应用中文档不可复用的SQL约束实现咨询

Alright, let's work through this constraint problem to ensure a document can't be reused across applications owned by different users. Here are a few practical, robust solutions tailored to your schema:

1. Database-Level Trigger (Most Reliable) #

This approach enforces the constraint directly at the database layer, preventing invalid data even if there's a slip-up in application logic or concurrent requests.

First, create a trigger function that checks for conflicting associations before inserting/updating a document:

CREATE OR REPLACE FUNCTION check_document_unique_across_users()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the same document value is already linked to an application from a different user
    IF EXISTS (
        SELECT 1
        FROM pan p
        JOIN applications a ON p.application_id = a.id
        WHERE p.value = NEW.value
        AND a.user_id != (SELECT user_id FROM applications WHERE id = NEW.application_id)
    ) THEN
        RAISE EXCEPTION 'Document with value % cannot be assigned to applications from different users', NEW.value;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then attach this trigger to your document table (repeat for other document types like pan):

CREATE TRIGGER trigger_check_pan_unique_across_users
BEFORE INSERT OR UPDATE ON pan
FOR EACH ROW EXECUTE FUNCTION check_document_unique_across_users();

This trigger will block any insert/update that tries to link a document to an application owned by a user who doesn't already have that document linked to one of their apps.

2. Add User ID to Document Tables + Unique Constraint #

If you're open to modifying your schema, adding a user_id directly to each document table creates a clearer data structure and lets you use a unique constraint instead of a trigger.

Step 1: Add User ID Column and Foreign Key #

ALTER TABLE pan 
ADD COLUMN user_id bigint NOT NULL,
ADD CONSTRAINT fk_pan_user FOREIGN KEY (user_id) REFERENCES users(id);

Step 2: Enforce Unique Document per User #

ALTER TABLE pan 
ADD CONSTRAINT unique_pan_value_user UNIQUE (value, user_id);

Step 3: Ensure User ID Matches Linked Application #

Add a check constraint (or trigger) to guarantee the document's user_id matches the linked application's user_id:

ALTER TABLE pan 
ADD CONSTRAINT check_pan_user_matches_application
CHECK (user_id = (SELECT user_id FROM applications WHERE id = pan.application_id));

Note: For PostgreSQL, check constraints with subqueries work, but if you need to handle updates to applications.user_id, a trigger would be more reliable to keep the document's user_id in sync.

3. Application-Level Validation (Complementary to Database Constraints) #

While database-level checks are critical, adding validation in your application code provides better user feedback before hitting the database. Here's a quick example (using pseudocode):

def link_document_to_application(document_value, application_id):
    # Fetch the target application and its owner
    target_app = get_application_by_id(application_id)
    if not target_app:
        raise ValueError("Application not found")
    
    # Check if the document is already used by another user's app
    conflicting_doc = get_document_by_value(document_value)
    if conflicting_doc:
        conflicting_app = get_application_by_id(conflicting_doc.application_id)
        if conflicting_app.user_id != target_app.user_id:
            raise ValueError("This document is already in use by another user's application")
    
    # Proceed to link the document
    create_document_association(document_value, application_id)

Important: Always pair application-level checks with database constraints—concurrent requests could bypass app logic, so the database acts as the final guard.

Bonus: Strict One-Document-to-One-Application (If Needed) #

If you actually want to prevent a document from being reused across any applications (even the same user's), simplify things with a unique constraint on the document's value:

ALTER TABLE pan ADD CONSTRAINT unique_pan_value UNIQUE (value);

This ensures each document is linked to exactly one application, regardless of user.

内容的提问来源于stack exchange，提问作者Amit