0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

【検証】テーブルをリネームした際、プライマリーキーのリネームは必要?

Last updated at Posted at 2024-04-21

【環境】
・MacOS
・PostgreSQL14

【前提】
以下の手順でテーブルを再構築をする想定での検証

  1. 「info」テーブルを「info_bk」へリネーム
  2. 新規で「info」テーブルを作成(新規カラムを追加)
  3. テーブル「info_bk」を削除
  4. 「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。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?