PostgreSQL配置表约束需求：企业配置关联表唯一性约束实现

Alright, let's break down how to solve this problem properly. Your goal is to enforce two mutually exclusive states for each config_id in the company_config_relations table:

• Either a single global record (where company_id is NULL, applying to all companies)
• Or multiple company-specific records (each with a unique non-NULL company_id)
  But never both states at the same time. The two indexes you tried handle parts of this but don't prevent mixing the two states—here's how to fix that:

Step 1: Keep the Existing Unique Indexes (for Single-State Integrity) #

First, retain the two indexes you already have—they're still necessary to ensure uniqueness within each state:

-- Ensure only one global record per config_id
CREATE UNIQUE INDEX config_unique_all_companies ON company_config_relations (config_id) WHERE company_id IS NULL;

-- Ensure no duplicate company-specific records per config_id
CREATE UNIQUE INDEX config_company_unique ON company_config_relations (config_id, company_id) WHERE company_id IS NOT NULL;

These indexes prevent:

• Multiple global records for the same config_id
• Duplicate company-specific entries for the same config_id + company_id pair

Step 2: Add a Constraint to Block Mixed States #

The missing piece is preventing a config_id from having both a global record and company-specific records. The solution depends on your database system:

For PostgreSQL (Using Exclude Constraints) #

PostgreSQL's EXCLUDE constraint is perfect for this—it lets you define mutually exclusive conditions. We'll use a case statement to tag records as "global" or "specific", then ensure those tags can't coexist for the same config_id:

ALTER TABLE company_config_relations
ADD CONSTRAINT config_mutually_exclusive_state
EXCLUDE USING gist (
    config_id WITH =,
    (CASE WHEN company_id IS NULL THEN 'global' ELSE 'specific' END) WITH <>
);

This constraint throws an error if you try to add a global record when company-specific ones exist, or vice versa.

For MySQL (Using Triggers) #

MySQL doesn't support exclude constraints, so we'll use triggers to enforce the rule on insert and update:

-- Trigger for insert operations
DELIMITER //
CREATE TRIGGER check_config_state_insert
BEFORE INSERT ON company_config_relations
FOR EACH ROW
BEGIN
    DECLARE has_global INT;
    DECLARE has_specific INT;

    -- Check if the config already has a global record
    SELECT COUNT(*) INTO has_global
    FROM company_config_relations
    WHERE config_id = NEW.config_id AND company_id IS NULL;

    -- Check if the config already has company-specific records
    SELECT COUNT(*) INTO has_specific
    FROM company_config_relations
    WHERE config_id = NEW.config_id AND company_id IS NOT NULL;

    -- Block global insert if specific records exist
    IF NEW.company_id IS NULL AND has_specific > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot add global config: this config already has company-specific entries';
    END IF;

    -- Block specific insert if global record exists
    IF NEW.company_id IS NOT NULL AND has_global > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot add company-specific config: this config already has a global entry';
    END IF;
END //
DELIMITER ;

-- Trigger for update operations
DELIMITER //
CREATE TRIGGER check_config_state_update
BEFORE UPDATE ON company_config_relations
FOR EACH ROW
BEGIN
    DECLARE has_global INT;
    DECLARE has_specific INT;

    -- Exclude the current record from checks (since we're updating it)
    SELECT COUNT(*) INTO has_global
    FROM company_config_relations
    WHERE config_id = NEW.config_id AND company_id IS NULL AND id != NEW.id;

    SELECT COUNT(*) INTO has_specific
    FROM company_config_relations
    WHERE config_id = NEW.config_id AND company_id IS NOT NULL AND id != NEW.id;

    -- Block updating to global if specific records exist
    IF NEW.company_id IS NULL AND has_specific > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update to global config: this config already has company-specific entries';
    END IF;

    -- Block updating to specific if global record exists
    IF NEW.company_id IS NOT NULL AND has_global > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update to company-specific config: this config already has a global entry';
    END IF;
END //
DELIMITER ;

How This Works #

Combining these components ensures:

• Each config_id can only exist in one state (global or specific)
• Within each state, records are unique as required

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