-- 07_foreign_keys_on_delete.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Foreign keys and ON DELETE behavior in banking.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.
SET search_path = bank, public;

-- Demonstrate ON DELETE behavior: restrict deleting a customer with accounts
ALTER TABLE bank.accounts
  DROP CONSTRAINT IF EXISTS accounts_customer_id_fkey,
  ADD CONSTRAINT accounts_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES bank.customers(customer_id) ON DELETE RESTRICT;

-- For transactions, delete is rarely allowed; keep RESTRICT (default).
