Missing Foreign Keys

-- Smell: _id column without FK
CREATE TABLE comments (
  comment_id  bigint PRIMARY KEY,
  post_id     bigint NOT NULL
);
 
-- Fix: add FK (after cleaning orphans)
DELETE FROM comments c
WHERE NOT EXISTS (SELECT 1 FROM posts p WHERE p.post_id = c.post_id);
 
ALTER TABLE comments
  ADD CONSTRAINT comments_post_id_fkey
  FOREIGN KEY (post_id) REFERENCES posts (post_id) ON DELETE CASCADE
  NOT VALID;
 
ALTER TABLE comments VALIDATE CONSTRAINT comments_post_id_fkey;

BEGIN;
 
CREATE TABLE authors (author_id bigint PRIMARY KEY, name text NOT NULL);
CREATE TABLE posts (
  post_id    bigint PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (author_id)
);
 
-- Application-only child table (defect)
CREATE TABLE comments_bad (
  comment_id  bigint PRIMARY KEY,
  post_id     bigint NOT NULL
);
 
INSERT INTO posts VALUES (1, 1);
INSERT INTO comments_bad VALUES (100, 1);
INSERT INTO comments_bad VALUES (101, 999);  -- orphan - no post 999
 
-- Detection
SELECT comment_id, post_id FROM comments_bad c
WHERE NOT EXISTS (SELECT 1 FROM posts p WHERE p.post_id = c.post_id);
 
-- Remediation path
DELETE FROM comments_bad WHERE post_id = 999;
 
ALTER TABLE comments_bad
  ADD CONSTRAINT comments_bad_post_id_fkey
  FOREIGN KEY (post_id) REFERENCES posts (post_id) ON DELETE CASCADE;
 
-- Prove enforcement
INSERT INTO comments_bad VALUES (102, 888);  -- ERROR: violates FK
 
COMMIT;

-- Find all columns named %_id without FK (heuristic audit)
SELECT
  c.table_schema, c.table_name, c.column_name
FROM information_schema.columns c
LEFT JOIN information_schema.key_column_usage k
  ON k.table_schema = c.table_schema
 AND k.table_name = c.table_name
 AND k.column_name = c.column_name
WHERE c.column_name LIKE '%\_id' ESCAPE '\'
  AND c.table_schema = 'public';
-- Manual review required - not all _id columns are FKs