Why Does PostgreSQL Throw 'No Unique Constraint for Referenced Table 'bar'' and How Can I Fix It?

Decoding the PostgreSQL Error: "No Unique Constraint for Referenced Table 'bar'"

PostgreSQL 9.1 users often encounter this perplexing error when building interconnected tables: "there is no unique constraint matching given keys for referenced table "bar"." This guide explains the root cause and solution.

The error typically arises when tables (e.g., foo, bar, baz) are linked via foreign keys. For instance, bar might reference foo using a foreign key (foo_fk), and baz references bar using another foreign key (bar_fk).

The problem lies within the bar table. While a unique index might exist on a combined column (like foo_fk and name), a unique constraint is missing on the name column itself.

Consider this scenario: Two rows in bar share the same name value (e.g., 'ams'). Attempting to insert a row into baz referencing 'ams' in bar_fk creates ambiguity. PostgreSQL can't determine the correct bar row, resulting in the error.

The Solution: Enforce Uniqueness

The fix is straightforward: add a unique constraint to the name column in the bar table. Use this SQL command:

<code class="language-sql">ALTER TABLE bar ADD UNIQUE (name);</code>

This ensures each bar row has a unique name, resolving ambiguity and enabling successful foreign key relationships. Your table structure will now function as intended.

The above is the detailed content of Why Does PostgreSQL Throw 'No Unique Constraint for Referenced Table 'bar'' and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!