PostgreSQL の外部キー制約の簡単なサンプルメモ

サンプルテーブル

テーブル作成

# create table groups ( id serial primary key, name text );
# create table users ( id serial, user_name text, group_id int references groups(id) );
# \d groups;
                            Table "public.groups"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 id     | integer |           | not null | nextval('groups_id_seq'::regclass)
 name   | text    |           |          | 
Indexes:
    "groups_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "users" CONSTRAINT "users_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id)
# \d users;
                              Table "public.users"
  Column   |  Type   | Collation | Nullable |              Default              
-----------+---------+-----------+----------+-----------------------------------
 id        | integer |           | not null | nextval('users_id_seq'::regclass)
 user_name | text    |           |          | 
 group_id  | integer |           |          | 
Foreign-key constraints:
    "users_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id)

データ挿入

グループが無い状態でユーザーを作成

# insert into users values (1, 'kure', 1);
ERROR:  insert or update on table "users" violates foreign key constraint "users_group_id_fkey"
DETAIL:  Key (group_id)=(1) is not present in table "groups".

★グループが無いからエラーになる

グループを作ってからユーザーを作成

# insert into groups values (1, 'kureg');
INSERT 0 1
# insert into users values (1, 'kure', 1);
INSERT 0 1

★OK

データ削除

先にグループを削除

# delete from groups where id = 1;
ERROR:  update or delete on table "groups" violates foreign key constraint "users_group_id_fkey" on table "users"
DETAIL:  Key (id)=(1) is still referenced from table "users".

★使用しているユーザーがあるのでエラーになる

# delete from users where id = 1;
DELETE 1
# delete from groups where id = 1;
DELETE 1

★OK

参考

外部キー https://www.postgresql.jp/document/9.4/html/tutorial-fk.html