PostgreSQLで外部キーを外して削除する

MySQLで外部キーを無効にするのは簡単なのだが、PostgreSQLだと少々手こずったので纏めておく。


まずは環境構築。自分が何かを検証する時は必ずdockerでやるので今回も例に漏れずやる。

docker-compose.yml:

version: '3'
services:
  db:
    image: postgres:11.2
    ports:
      - "15432:5432"

最小サンプルとして以下のSQLを用意した。

CREATE TABLE department(
    id integer PRIMARY KEY,
    name varchar(10)
);
CREATE TABLE staff(
  id integer,
  department_id integer,
  name varchar(10),
  FOREIGN KEY (department_id) REFERENCES department(id)
);

INSERT INTO department VALUES (1, 'Sales'), (2, 'Accounting'), (3, 'Marketing');
INSERT INTO staff VALUES (1, 1, 'Yamada'), (2, 2, 'Suzuki'), (3, 3, 'Honda');

foreign keyは department_iddepeartment has one(many) staff というrelationだ。

describeするとこうなる。departmentには Referenced by, staffには Foreign-key constraints が追加される。

postgres=# \d department
                    Table "public.department"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(10) |           |          | 
Indexes:
    "department_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "staff" CONSTRAINT "staff_department_id_fkey" FOREIGN KEY (department_id) REFERENCES department(id)

postgres=# \d staff
                          Table "public.staff"
    Column     |         Type          | Collation | Nullable | Default 
---------------+-----------------------+-----------+----------+---------
 id            | integer               |           |          | 
 department_id | integer               |           |          | 
 name          | character varying(10) |           |          | 
Foreign-key constraints:
    "staff_department_id_fkey" FOREIGN KEY (department_id) REFERENCES department(id)

今の目標はdepartmentのデータを削除することだ。普通に削除しようとするともちろん外部キー制約でできない。

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

アプローチ方法は二種類。

外部キー自体を削除してDELETEを流す

一度外部キー自体を削除してしまえばデータは削除できるよね、ということでALTER TABLEで流してみた。

ALTER TABLE - PostgreSQL 7.2.3 リファレンスマニュアル

BEGIN;
ALTER TABLE staff DROP CONSTRAINT staff_department_id_fkey;
DELETE FROM department;
ALTER TABLE staff ADD foreign key (department_id) references department(id);
COMMIT;

外部キーを無効にしてDELETEを流す

MySQLの SET FOREIGN_KEY_CHECKS=0; みたいなのはPostgreSQLには無いみたいだが(要調査)、ALTER TABLEでTRIGGERを無効にする処理はあるみたいだ。

ALTER TABLE - PostgreSQL 9.5.4文書

BEGIN;
ALTER TABLE department DISABLE TRIGGER ALL;
ALTER TABLE staff DISABLE TRIGGER ALL;

delete from department;

ALTER TABLE department ENABLE TRIGGER ALL;
ALTER TABLE staff ENABLE TRIGGER ALL;
COMMIT;

これでできた。