Why Does My Postgres 9.1 Foreign Key Reference Fail with a 'Unique Constraint' Error?

Resolving "Unresolved unique constraint" error in PostgreSQL 9.1 foreign key references

When creating a table structure containing foreign key references in PostgreSQL 9.1, you may encounter unexpected errors. This error results from inconsistent constraint configuration.

The SQL code in question involves creating three tables: foo, bar, and baz. The foo table has a primary key on the name column, while the bar table defines the foo_fk column as a foreign key referencing foo(name). However, the error message complains that the name column referenced in the bar table lacks a unique constraint.

To understand this problem, consider the scenario where multiple rows in the bar table share the same name value, as shown in the following example:

INSERT INTO bar (foo_fk, name) VALUES ('alice', 'ams');
INSERT INTO bar (foo_fk, name) VALUES ('bob', 'ams');

Now, if we try to insert a row into the baz table and reference the ambiguous ams value in the bar table, PostgreSQL will not be able to uniquely identify the target row. This ambiguity arises because PostgreSQL cannot determine which row in the bar table should be referenced by the foreign key.

The solution is to enforce the uniqueness of the name column in the bar table. By adding a unique constraint to the name column, we ensure that each row in the bar table has a different name value, thus removing ambiguity and allowing PostgreSQL to uniquely identify the referenced row.

The corrected SQL code including the necessary constraints is as follows:

CREATE TABLE foo (
    name        VARCHAR(256) PRIMARY KEY
);

CREATE TABLE bar (
    pkey        SERIAL PRIMARY KEY,
    foo_fk      VARCHAR(256) NOT NULL REFERENCES foo(name),
    name        VARCHAR(256) NOT NULL,
    UNIQUE (name)  -- 添加唯一约束
);

CREATE TABLE baz(
    pkey        SERIAL PRIMARY KEY,
    bar_fk      VARCHAR(256) NOT NULL REFERENCES bar(name),
    name        VARCHAR(256)
);

With this unique constraint, PostgreSQL can now successfully create the table structure and avoid the "No unique constraint matching the given key" error.

The above is the detailed content of Why Does My Postgres 9.1 Foreign Key Reference Fail with a 'Unique Constraint' Error?. For more information, please follow other related articles on the PHP Chinese website!