PostgreSQL 9.4：如何让表引用user_cars表的唯一约束test

Got it, let's break down how to create your mappings table that references the test unique constraint on user_cars.

First, remember that PostgreSQL lets foreign keys target unique constraints (not just primary keys) as long as the columns in your foreign key match the columns in the unique constraint—same number, same data types, same order.

Correct Table Creation Statement #

Here's the full working code to create your mappings table:

CREATE TABLE mappings (
    other_id CHARACTER(9) NOT NULL,
    -- Match the columns from the unique constraint in user_cars
    user_id INTEGER NOT NULL,
    car CHARACTER VARYING(255) NOT NULL,
    -- Optional: Set a primary key for mappings (adjust as needed)
    PRIMARY KEY (other_id),
    -- Define the foreign key referencing the unique constraint
    FOREIGN KEY (user_id, car)
        REFERENCES user_cars (user_id, car)
        -- Optional: Add delete/update behavior (matches user_cars' ON DELETE CASCADE)
        ON DELETE CASCADE
);

What's Happening Here? #

• We include user_id and car in mappings with the exact same data types as in user_cars (INTEGER for user_id, since SERIAL maps to INTEGER, and VARCHAR(255) for car).
• When we write REFERENCES user_cars (user_id, car), PostgreSQL automatically recognizes that this pair maps to your test unique constraint—you don't even need to explicitly name the constraint (though you can if you want, see below).
• The optional ON DELETE CASCADE ensures that if a record in user_cars is deleted, any related records in mappings are also deleted (mirroring the behavior of your user_cars table's foreign key to users).

Explicitly Naming the Unique Constraint (Optional) #

If you want to explicitly reference the test constraint by name (for clarity), you can modify the foreign key clause like this:

FOREIGN KEY (user_id, car)
    REFERENCES user_cars CONSTRAINT test
    ON DELETE CASCADE

Key Notes to Avoid Errors #

• Make sure the order of columns in your foreign key matches the order in the test constraint (user_id first, then car).
• You can't reference just one column from the unique constraint—your foreign key must include both user_id and car, since the uniqueness is enforced on the pair.
• Double-check data types: user_id in mappings must be INTEGER (not SERIAL—SERIAL is just a shortcut for creating an auto-incrementing integer column with a sequence, but the underlying type is INTEGER).

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