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.
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.
BeforeMerge scans your pull requests against this rule and 4+ others. Get actionable feedback before code ships.
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:
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.
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: no foreign key — the database allows orphaned orders
CREATE 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: foreign keys enforce referential integrity
CREATE 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 constraintFind columns that look like foreign keys but lack constraints:
-- Find columns ending in _id without foreign key constraints
SELECT c.table_name, c.column_name
FROM information_schema.columns c
WHERE 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
);*_id)