How to fix ERROR: insert or update on table violates foreign key constraint in PostgreSQL

-- Error example:
-- ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
-- DETAIL:  Key (customer_id)=(999) is not present in table "customers".

-- Query the constraint details:
SELECT constraint_name, table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 'orders' AND constraint_name LIKE '%customer_id%';

-- Get the referenced table and column:
SELECT 
  kcu.table_name,
  kcu.column_name,
  ccu.table_name AS referenced_table,
  ccu.column_name AS referenced_column
FROM information_schema.key_column_usage kcu
JOIN information_schema.constraint_column_usage ccu
  ON kcu.constraint_name = ccu.constraint_name
WHERE kcu.table_name = 'orders';

-- Check if the parent ID exists:
SELECT * FROM customers WHERE id = 999;

-- Find all distinct foreign key values in the child table:
SELECT DISTINCT customer_id FROM orders;

-- Find child records referencing non-existent parent IDs:
SELECT DISTINCT o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- WRONG ORDER - child before parent:
INSERT INTO orders (id, customer_id, amount) VALUES (1, 999, 100.00);
INSERT INTO customers (id, name) VALUES (999, 'John');
-- Result: ERROR 23503 on first insert

-- CORRECT ORDER - parent before child:
INSERT INTO customers (id, name) VALUES (999, 'John');
INSERT INTO orders (id, customer_id, amount) VALUES (1, 999, 100.00);
-- Success

-- For bulk imports from a CSV/JSON, sort by table dependencies:
-- 1. Load all parent tables first
-- 2. Then load child tables
-- 3. Wrap in a transaction to ensure atomicity:
BEGIN;
  INSERT INTO customers (id, name) SELECT id, name FROM staging_customers;
  INSERT INTO orders (id, customer_id, amount) SELECT id, customer_id, amount FROM staging_orders;
COMMIT;

-- Race condition WITHOUT transaction:
-- Session A: DELETE FROM customers WHERE id = 999;
-- Session B: INSERT INTO orders (customer_id) VALUES (999); -- Fails!

-- Solution: Use transactions to ensure atomicity:
BEGIN;
  -- Ensure no one is inserting children while we delete the parent
  DELETE FROM customers WHERE id = 999;
  -- This succeeds only if no pending inserts reference customer 999
COMMIT;

-- Or if adding a child, lock the parent first:
BEGIN;
  SELECT * FROM customers WHERE id = 999 FOR UPDATE; -- Lock the row
  -- Now other sessions can't delete this customer while we insert an order
  INSERT INTO orders (customer_id, amount) VALUES (999, 100.00);
COMMIT;

-- View the constraint definition:
SELECT
  constraint_name,
  table_name,
  column_name,
  referenced_table_name,
  referenced_column_name
FROM information_schema.key_column_usage
WHERE table_name = 'orders';

-- Get the full constraint definition including actions:
SELECT 
  con.conname as constraint_name,
  con.confdeltype as delete_action,
  con.confupdtype as update_action
FROM pg_constraint con
WHERE con.conrelid = 'orders'::regclass;

-- Option A: Delete children first, then parent:
DELETE FROM orders WHERE customer_id = 999;
DELETE FROM customers WHERE id = 999;

-- Option B: Use CASCADE to auto-delete children:
-- (This requires recreating the constraint)
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

-- Now deleting parent auto-deletes children:
DELETE FROM customers WHERE id = 999;

-- Option C: Find which children reference the parent:
SELECT * FROM orders WHERE customer_id = 999;

-- Ensure parent exists with INSERT...ON CONFLICT:
INSERT INTO customers (id, name) 
VALUES (999, 'John')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- Then safely insert the child:
INSERT INTO orders (customer_id, amount)
VALUES (999, 100.00);

-- Or use deferred constraints in a transaction:
BEGIN;
  SET CONSTRAINTS ALL DEFERRED;
  INSERT INTO orders (customer_id, amount) VALUES (999, 100.00);
  INSERT INTO customers (id, name) VALUES (999, 'John');
  -- Constraint checking happens at COMMIT
COMMIT;