Define foreign key constraints for all table relationships. Without foreign keys, the database allows orphaned rows (e.g., an order referencing a deleted customer), corrupting data integrity silently.
Why This Matters
Without foreign key constraints, the database allows references to rows that don't exist. You can insert an order with a customer_id that has no matching customer. You can delete a customer while their orders still reference them. These orphaned references cause NULL errors in JOINs, broken UI displays, and data that silently becomes meaningless. Foreign keys prevent this at the database level — the only place where data integrity can be guaranteed.
Foreign keys are database-enforced guarantees that a reference points to a real row. Without them, referential integrity depends entirely on application code — every INSERT, UPDATE, and DELETE must correctly maintain relationships. This fails because:
Application bugs skip validation
Direct database access (migrations, scripts, admin tools) bypasses application code
Concurrent operations create race conditions where a referenced row is deleted between the check and the insert
Over time, orphaned references accumulate silently
The result is data corruption that is invisible until someone queries across tables and gets unexpected NULLs, missing JOINs, or broken counts. Foreign keys prevent this at the only level that matters: the database itself.
The rule
Every column that references another table's primary key must have a FOREIGN KEY constraint. Specify the ON DELETE behavior explicitly:
CASCADE — delete child rows when the parent is deleted (orders when customer is deleted)
SET NULL — set the reference to NULL (optional relationships)
RESTRICT — prevent deletion of the parent while children exist (safest default)
Bad example
-- BAD: no foreign key — the database allows orphaned ordersCREATE TABLE orders ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), customer_id uuid NOT NULL, -- no REFERENCES constraint! product_id uuid NOT NULL, -- no REFERENCES constraint! quantity integer NOT NULL);-- These operations succeed silently, creating corrupt data:INSERT INTO orders (customer_id, product_id, quantity)VALUES ('nonexistent-uuid', 'also-nonexistent', 5);DELETE FROM customers WHERE id = 'has-existing-orders';
Good example
-- GOOD: foreign keys enforce referential integrityCREATE TABLE orders ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), customer_id uuid NOT NULL REFERENCES customers(id) ON DELETE RESTRICT, quantity integer NOT NULL);CREATE TABLE order_items ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), order_id uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT, quantity integer NOT NULL);-- Now these operations are rejected:-- INSERT with nonexistent customer_id → ERROR: violates foreign key constraint-- DELETE customer with orders → ERROR: violates foreign key constraint
How to detect
Find columns that look like foreign keys but lack constraints:
-- Find columns ending in _id without foreign key constraintsSELECT c.table_name, c.column_nameFROM information_schema.columns cWHERE c.column_name LIKE '%_id' AND c.table_schema = 'public' AND NOT EXISTS ( SELECT 1 FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' AND kcu.table_name = c.table_name AND kcu.column_name = c.column_name );
Remediation
Identify all columns that reference other tables (typically named *_id)
Add REFERENCES constraints with explicit ON DELETE behavior
Choose ON DELETE behavior carefully: RESTRICT for critical references, CASCADE for dependent data, SET NULL for optional references
Run the constraint on existing data — fix any orphaned rows first
Add an index on every foreign key column for JOIN performance