【環境】
・MacOS
・PostgreSQL14
【前提】
以下の手順でテーブルを再構築をする想定での検証
- 「info」テーブルを「info_bk」へリネーム
- 新規で「info」テーブルを作成(新規カラムを追加)
- テーブル「info_bk」を削除
- 「info」テーブルに主キー設定を定義
【検証したい内容】
上記の手順でテーブルを再構築した際、
再構築前後のプライマリーキーの名前は同一となるはずだが、
プライマリーキーのリネームは必要?
【検証】
以下のSQLで検証用テーブルを作成
CREATE TABLE info(key int,
name text,
PRIMARY KEY(key)
);
テストデータを1行追加
INSERT INTO info(key, name)values(1, 'テスト');
INSERT 0 1
試しに重複データを追加
INSERT INTO info(key, name)values(1, 'テスト');
ERROR: duplicate key value violates unique constraint "info_pkey"
DETAIL: Key (key)=(1) already exists.
エラーが出て登録できないことを確認。
次に「info」テーブルの主キー設定の確認
test=# \d info;
Table "public.info"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
key | integer | | not null |
name | text | | |
Indexes:
"info_pkey" PRIMARY KEY, btree (key)
SELECT ccu.table_name as table_name,
ccu.column_name as COLUMN_NAME,
ccu.constraint_name as constraint_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON (tc.table_catalog=ccu.table_catalog
and tc.table_schema=ccu.table_schema
and tc.table_name=ccu.table_name
and tc.constraint_name=ccu.constraint_name
)
WHERE tc.table_catalog='test'
and tc.table_schema='public'
and tc.constraint_type='PRIMARY KEY';
table_name | column_name | constraint_name
------------+-------------+-----------------
info | key | info_pkey
(1 row)
設定されていることを確認
次にテーブルをリネーム
ALTER TABLE info RENAME TO info_bk;
ALTER TABLE
\d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | info_bk | table | yuma
登録されているプライマリーキーを確認
SELECT ccu.table_name as table_name,
ccu.column_name as COLUMN_NAME,
ccu.constraint_name as constraint_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON (tc.table_catalog=ccu.table_catalog
and tc.table_schema=ccu.table_schema
and tc.table_name=ccu.table_name
and tc.constraint_name=ccu.constraint_name
)
WHERE tc.table_catalog='test'
and tc.table_schema='public'
and tc.constraint_type='PRIMARY KEY';
table_name | column_name | constraint_name
------------+-------------+-----------------
info_bk | key | info_pkey
(1 row)
プライマリーキー「info_pkey」が
リネーム後の「info_bk」に紐づいていることを確認
numberカラムを追加したinfoテーブルを作成
CREATE TABLE info(key int,
number int,
name text,
PRIMARY KEY(key)
);
CREATE TABLE
テーブルを確認
\d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | info | table | yuma
public | info_bk | table | yuma
(2 rows)
\d info;
Table "public.info"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
key | integer | | not null |
number | integer | | |
name | text | | |
Indexes:
"info_pkey1" PRIMARY KEY, btree (key)
登録されているプライマリーキーを確認
SELECT ccu.table_name as table_name,
ccu.column_name as COLUMN_NAME,
ccu.constraint_name as constraint_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON (tc.table_catalog=ccu.table_catalog
and tc.table_schema=ccu.table_schema
and tc.table_name=ccu.table_name
and tc.constraint_name=ccu.constraint_name
)
WHERE tc.table_catalog='test'
and tc.table_schema='public'
and tc.constraint_type='PRIMARY KEY';
table_name | column_name | constraint_name
------------+-------------+-----------------
info_bk | key | info_pkey
info | key | info_pkey1
(2 rows)
同名のため末尾に1が付与された「info_pkey1」が作成されていることを確認
最後にテーブルを削除
DROP TABLE info_bk;
DROP TABLE
もう一度プライマリーキーを確認
SELECT ccu.table_name as table_name,
ccu.column_name as COLUMN_NAME,
ccu.constraint_name as constraint_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON (tc.table_catalog=ccu.table_catalog
and tc.table_schema=ccu.table_schema
and tc.table_name=ccu.table_name
and tc.constraint_name=ccu.constraint_name
)
WHERE tc.table_catalog='test'
and tc.table_schema='public'
and tc.constraint_type='PRIMARY KEY';
table_name | column_name | constraint_name
------------+-------------+-----------------
info | key | info_pkey1
(1 row)
試しにエラーとなるデータを登録してみる
INSERT INTO info(key, number, name)values(1, 1 ,'テスト');
INSERT 0 1
INSERT INTO info(key, number, name)values(1, 1 ,'テスト');
ERROR: duplicate key value violates unique constraint "info_pkey1"
DETAIL: Key (key)=(1) already exists.
期待通りの結果となったため、
正常にプライマリー制約が機能していると判断する。
【検証結果】
PostgreSQLのプライマリーキー制約はテーブルに紐づいている。
そのため、列の追加などでテーブルを再構築する際、
プライマリーキーのリネームは不要。
ただし、テーブル構築後にALTER文で制約を追加する場合は同名のものが存在するため、エラーとなる。あくまでテーブル作成時に制約を設定する場合のみに限られる。
注意点として、同名のプライマリーキーが存在する場合、
末尾に1などの数字(おそらく連番)が付与される。
これを良しとしない場合は
テーブルのリネームとセットでプライマリーキーのセットを行うこと。
もしくは、リネーム後のテーブルを削除後に
ALTER文でプライマリー制約を追加すればOK。