The Quick Fix
PostgreSQL is blocking your Foreign Key because the column you're pointing to isn't guaranteed to be unique. For a relationship to work, the parent table column (like order_id) must have a PRIMARY KEY or a UNIQUE constraint.
Run this command to add the missing constraint:
ALTER TABLE orders ADD CONSTRAINT orders_order_id_unique UNIQUE (order_id);
Once that is applied, your FOREIGN KEY creation will succeed immediately.
Why This Error Happens
Suppose your application manages 5,000 daily transactions. You have an orders table and an order_items table. If you try to link them using a non-unique column, Postgres gets confused.
-- This parent table setup causes the issue
CREATE TABLE orders (
id SERIAL,
tracking_code TEXT -- No unique constraint here!
);
-- This child table creation will fail
CREATE TABLE order_items (
id SERIAL,
sku_name TEXT,
order_ref TEXT REFERENCES orders(tracking_code)
);
The database engine stops you with a matching given keys error. It does this because a Foreign Key is a pointer. If tracking_code allows two rows to have the same value, Postgres wouldn't know which specific order the item belongs to.
Under the Hood: The Rules of Engagement
Data integrity is non-negotiable in PostgreSQL. Even if your table currently has 100% unique values, the database requires a schema-level guarantee. It won't take your word for it; it needs a constraint.
Common triggers for this roadblock include:
- **Plain Columns:** You are referencing a standard column that lacks a Unique index.
- **Composite Key Mismatch:** You are linking two columns (e.g., `tenant_id` and `user_id`), but the parent table only defines them as unique individually.
- **Data Type Friction:** Trying to link a `BIGINT` to an `INT` can sometimes trigger misleading errors or performance hits.
Three Ways to Fix the Relationship
1. Add a Unique Constraint
Use this if the column must remain a TEXT or VARCHAR field but needs to be a valid reference target. This is common when using human-readable IDs like INV-2024-001.
ALTER TABLE orders ADD CONSTRAINT unique_tracking_code UNIQUE (tracking_code);
2. Reference the Primary Key (Best Practice)
In most production environments, it is safer and faster to reference the id column. Integer-based joins perform significantly better than string-based joins once you hit 100,000+ rows.
-- Change your child table to point to the ID instead
ALTER TABLE order_items
ADD CONSTRAINT fk_order_id
FOREIGN KEY (order_id)
REFERENCES orders(id);
3. Resolve Composite Key Issues
When your relationship depends on multiple columns, Postgres requires a single multi-column constraint. You cannot simply point to two separate unique columns.
The Fix:
-- Create one constraint that covers both columns
ALTER TABLE orders ADD CONSTRAINT store_order_composite_unique UNIQUE (store_id, order_id);
How to Verify Your Schema
Before retrying your migration, check the current state of your table using the psql interface. Type \d orders to see the table definition.
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
"unique_tracking_code" UNIQUE CONSTRAINT, btree (tracking_code)
If you don't see the UNIQUE CONSTRAINT label next to your target column, the Foreign Key will continue to fail. Once the index is visible, you are clear to proceed.
Final Checklist
- Does the parent column have a `UNIQUE` or `PRIMARY KEY` constraint?
- Do the data types match exactly (e.g., `UUID` to `UUID`)?
- If using a composite key, does the order of columns in your FK match the order in the Unique constraint?