How to fix 42830: invalid_foreign_key in PostgreSQL

-- Check constraints on the parent table:
SELECT
  tc.constraint_name,
  tc.constraint_type,
  kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'parent_table_name'
  AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE');

-- Add UNIQUE constraint to an existing column:
ALTER TABLE parent_table
  ADD CONSTRAINT unique_column_name UNIQUE (column_name);

CREATE TABLE parent_table (
  id SERIAL PRIMARY KEY,
  reference_code VARCHAR(50) UNIQUE,  -- Add UNIQUE constraint
  name TEXT
);

-- Now you can reference reference_code:
CREATE TABLE child_table (
  id SERIAL PRIMARY KEY,
  parent_code VARCHAR(50) REFERENCES parent_table(reference_code),
  data TEXT
);

-- INCORRECT - column count mismatch:
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  region_id INT,
  FOREIGN KEY (customer_id, region_id) REFERENCES customers(customer_id)
  -- ERROR: 2 columns referencing 1 column
);

-- CORRECT - matching column counts:
CREATE TABLE customers (
  customer_id INT,
  region_id INT,
  PRIMARY KEY (customer_id, region_id)  -- Composite primary key
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  region_id INT,
  FOREIGN KEY (customer_id, region_id) REFERENCES customers(customer_id, region_id)
  -- Both sides have 2 columns
);

-- Check column data types:
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name IN ('parent_table', 'child_table')
  AND column_name IN ('referenced_col', 'foreign_key_col');

-- Original attempt (fails - email not unique):
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255),
  name TEXT
);

CREATE TABLE sessions (
  session_id UUID PRIMARY KEY,
  user_email VARCHAR(255) REFERENCES users(email)  -- FAILS: email not unique
);

-- Solution 1: Make email unique:
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- Solution 2: Reference the primary key instead:
CREATE TABLE sessions (
  session_id UUID PRIMARY KEY,
  user_id INT REFERENCES users(id),  -- Reference the primary key
  created_at TIMESTAMP
);

-- Parent table with composite primary key:
CREATE TABLE products (
  product_id INT,
  warehouse_id INT,
  name TEXT,
  PRIMARY KEY (product_id, warehouse_id)  -- Composite PK
);

-- Child table must reference both columns:
CREATE TABLE inventory (
  id SERIAL PRIMARY KEY,
  product_id INT,
  warehouse_id INT,
  quantity INT,
  FOREIGN KEY (product_id, warehouse_id)
    REFERENCES products(product_id, warehouse_id)  -- Both columns together
);

-- INCORRECT - referencing only one column of a composite key:
CREATE TABLE inventory_wrong (
  id SERIAL PRIMARY KEY,
  product_id INT REFERENCES products(product_id)  -- FAILS
);