PostgreSQL 設定外鍵 foreign key

PostgreSQL設定外鍵(foreign key)的方式如下。

例如有兩個資料表department及employee兩者關係如下。employee.department_id外鍵參考到department.id。

PostgreSQL使用REFERENCES定義欄位的外鍵。

下面建立資料表employee時在department_id欄位以REFERENCES定義參考department.id的外鍵。

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   department_id bigint REFERENCES department (id), -- 定義外鍵
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   created_at timestamp NOT NULL
);

若新增一筆employee資料其departmentid值不存在於department.id時會因外鍵限制而無法成功新增。

postgres=# INSERT INTO "employee" ("name", "department_id", "age", "created_at") VALUES ('john', 13, 33, now());
ERROR:  insert or update on table "employee" violates foreign key constraint "employee_department_id_fkey"
DETAIL:  Key (department_id)=(13) is not present in table "department".

同樣地，若刪除一筆department資料其id已被employee.department_id參照時會因為外鍵限制而無法成功刪除。

postgres=# DELETE FROM department WHERE id = 1;
ERROR:  update or delete on table "department" violates foreign key constraint "employee_department_id_fkey" on table "employee"
DETAIL:  Key (id)=(1) is still referenced from table "employee".

下面語法可查詢employee資料表設定的外鍵。

SELECT
    tc.table_schema,
    tc.constraint_name,
    tc.constraint_type,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' 
    AND tc.table_name = 'employee';

查詢結果。

 table_schema |      constraint_name       | constraint_type | table_name |  column_name  | foreign_table_schema | foreign_table_name | foreign_column_name
--------------+----------------------------+-----------------+------------+---------------+----------------------+--------------------+---------------------
 public       | employee_department_id_fkey| FOREIGN KEY     | employee   | department_id | public               | department         | id

• PostgreSQL Doc - Foreign Keys
• PostgreSQL Doc - Foreign Keys
• How to list table foreign keys
• PostgreSQL 查詢資料表欄位限制