What is causing ERROR: there is no unique constraint matching given keys for referenced table?

The "ERROR: there is no unique constraint matching given keys for referenced table" in PostgreSQL occurs when creating a foreign key constraint, but the referenced column is not unique or not a primary key.

Causes of the Error

1. Referencing a Non-Unique Column

• When creating a foreign key, PostgreSQL requires the referenced column to be a primary key or have a unique constraint.
  

Example of a failing scenario:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT
);
CREATE TABLE invoices (
    invoice_id SERIAL PRIMARY KEY,
    customer_name TEXT REFERENCES orders(customer_name)  -- Error occurs here
);

customer_name is not unique in orders, so it cannot be a foreign key reference.

2. Missing a Unique Constraint on the Referenced Column

The referenced column must have either a PRIMARY KEY or UNIQUE constraint.

Solution: Add a unique constraint.

  ALTER TABLE orders ADD CONSTRAINT unique_customer UNIQUE(customer_name);

OR

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT UNIQUE  -- Now it's a valid reference
);

3. Referencing a Composite Key Without a Unique Constraint

If referencing multiple columns, they must form a composite unique constraint.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INT,
    customer_id INT,
    UNIQUE (product_id, customer_id)
);
CREATE TABLE invoices (
    invoice_id SERIAL PRIMARY KEY,
    product_id INT,
    customer_id INT,
    FOREIGN KEY (product_id, customer_id) REFERENCES orders(product_id, customer_id)
);

How to Fix

✔ Ensure the referenced column has a PRIMARY KEY or UNIQUE constraint.

✔ Use composite unique constraints if referencing multiple columns.

✔ Check for typos in column names when defining foreign keys.

Let me know if you need more details!

The error "ERROR: There is no unique constraint matching given keys for referenced table" typically occurs when trying to create a foreign key constraint in a database table, but the referenced column(s) do not have a unique constraint defined in the referenced table.

To resolve this issue, you can either:

1. Add a unique constraint to the referenced column(s) in the referenced table.
   
2. Ensure that the foreign key column(s) in the referencing table reference a unique column(s) in the referenced table.
   

By doing either of these, you'll establish a relationship between the tables that adheres to the constraints required by the database schema.