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
沒有留言:
張貼留言