Got it, let's break down your requirements and work through the best ways to enforce these database constraints:

First, let's restate your rules clearly to make sure we're on the same page:

• Existing constraint: Your composite primary key (id, code, date) already ensures no duplicate rows share the exact same id, code, and date combination (this covers your first allowed example where the same id+code has multiple dates).
• New required constraint: A single code must map to exactly one id — no two different ids can ever be associated with the same code (this blocks the invalid case you showed).

Here are the most practical solutions:

1. Normalize with a Lookup Table (Recommended) #

This is the cleanest, most scalable approach that aligns with database normalization principles. It makes the code-to-id relationship explicit and easy to maintain:

Step 1: Create a code-to-id mapping table #

This table will enforce that each code is linked to only one id (since code is the primary key, it can't be duplicated):

-- Adjust data types (VARCHAR, INT) to match your actual schema
CREATE TABLE code_id_mappings (
    code VARCHAR(50) PRIMARY KEY,
    id INT NOT NULL
);

Step 2: Add a foreign key to your original table #

Modify your main table to reference the mapping table. This ensures every row in your main table uses a valid code-id pair that already exists in the lookup table:

ALTER TABLE your_main_table
ADD CONSTRAINT fk_code_id FOREIGN KEY (code, id) 
REFERENCES code_id_mappings(code, id);

Your original composite primary key (id, code, date) stays intact, so you can still have multiple rows for the same id+code with different dates (your first allowed scenario). And the lookup table ensures no code is ever linked to more than one id.

2. Use Database Triggers (No Table Structure Changes) #

If you can't modify your table structure, triggers let you check the constraint during insert or update operations. Here's an example for MySQL:

DELIMITER //
-- Trigger to block invalid inserts
CREATE TRIGGER check_code_id_insert BEFORE INSERT ON your_main_table
FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT 1 FROM your_main_table 
        WHERE code = NEW.code AND id != NEW.id
    ) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Error: A code cannot be associated with multiple IDs';
    END IF;
END //

-- Trigger to block invalid updates
CREATE TRIGGER check_code_id_update BEFORE UPDATE ON your_main_table
FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT 1 FROM your_main_table 
        WHERE code = NEW.code AND id != NEW.id
    ) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Error: A code cannot be associated with multiple IDs';
    END IF;
END //
DELIMITER ;

Note: Trigger syntax varies by database — for example, PostgreSQL uses RAISE EXCEPTION instead of SIGNAL. Triggers work, but they can add overhead and are less visible than the lookup table approach.

3. Database-Specific Constraints (Limited Use Cases) #

Some databases support advanced constraints, but most won't let you enforce the code-to-id dependency without breaking your allowed multi-date rows. For example, a unique index on (code) would block valid rows with the same code but different dates, so that's not useful here. The lookup table is still the most reliable choice.

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