Why Does My PostgreSQL Foreign Key Reference Fail with 'ERROR: there is no unique constraint matching given keys for referenced table 'bar''?

PostgreSQL Foreign Key Constraint Failure: A Unique Constraint Issue

This article addresses a common PostgreSQL 9.1 foreign key constraint error: "ERROR: there is no unique constraint matching given keys for referenced table "bar"". We'll analyze the cause and provide a solution.

The scenario involves three tables: "foo," "bar," and "baz." "foo" has a primary key ("name"). "bar" references "foo" via "foo_fk," has its own primary key ("pkey"), and a unique constraint on ("foo_fk", "name"). "baz" references "bar" using "bar_fk."

The error message signifies that the foreign key constraint in "baz" cannot be enforced because multiple rows in "bar" might share the same values for "foo_fk" and "name." This violates referential integrity, as the foreign key in "baz" cannot uniquely identify a corresponding row in "bar."

The problem stems from the unique constraint in "bar." While it's unique across the combination of "foo_fk" and "name," it doesn't guarantee uniqueness of "name" within "bar" independently. Therefore, multiple rows in "bar" could exist with the same "name" but different "foo_fk" values.

The Solution:

To resolve this, add a unique constraint to the "name" column in the "bar" table. This ensures that each "name" value is unique, thus enabling the foreign key constraint in "baz" to function correctly. After implementing this unique constraint, the foreign key relationship will be properly enforced, preventing the error and establishing the intended database structure.

The above is the detailed content of Why Does My PostgreSQL Foreign Key Reference Fail with 'ERROR: there is no unique constraint matching given keys for referenced table 'bar''?. For more information, please follow other related articles on the PHP Chinese website!