PostgreSQL ユニーク制約とユニークインデックス
こんにちは、Anti-Pattern Inc.の塚本です。
今回のブログは、あるカラムにユニーク制約を設定した時のお話です
制約とは
https://www.postgresql.jp/document/12/html/ddl-constraints.html
データ型は、テーブルに格納するデータの種類を限定するための方法です。 しかし、多くのアプリケーションでは、型が提供する制約では精密さに欠けます。 例えば、製品の価格が入る列には、おそらく正数のみを受け入れるようにする必要があります。 しかし、正数のみを受け入れるという標準のデータ型はありません。 また、他の列や行に関連して列データを制約したい場合もあります。 例えば、製品の情報が入っているテーブルでは、1つの製品番号についての行が2行以上あってはなりません。
このような問題を解決するため、SQLでは列およびテーブルに対する制約を定義することができます。 制約によってテーブル内のデータを自由に制御することができます。 制約に違反するデータを列に格納しようとすると、エラーとなります。 このことは、デフォルト値として定義された値を格納する場合にも適用されます。
PostgreSQL 12.4 第5章 データ定義 5.4. 制約からの引用です。
テーブル、カラムに特定の制限をかける事ができるって事ですね。
ALTER TABLE文で検証
例えばこのようなテーブル構成で、nameにユニーク制約を設定してみます
CREATE TABLE fruits
(
id serial NOT NULL,
name varchar NOT NULL,
PRIMARY KEY (id)
);
ALTER TABLE fruits ADD UNIQUE(name);
=> ALTER TABLE fruits ADD UNIQUE(name);
ALTER TABLE
=> \d fruits
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+------------------------
id | integer | | not null | nextval('fruits_id_seq'::regclass)
name | character varying | | not null |
Indexes:
"fruits_pkey" PRIMARY KEY, btree (id)
"fruits_name_key" UNIQUE CONSTRAINT, btree (name)
一意性制約を追加すると、制約で指定された列または列のグループに対して一意的なBツリーのインデックスが自動的に作られます。
PostgreSQL 12.4 第5章 データ定義 5.4. 制約からの引用です。
Indexesの最後の行に”UNIQUE CONSTRAINT”と出力されてますので、設定されている事がわかります。
システムカタログも確認してみます
=> select * from pg_class where relname = 'fruits';
-[ RECORD 1 ]-------+--------
relname | fruits
relnamespace | 16386
reltype | 8289445
reloftype | 0
relowner | 16385
relam | 0
relfilenode | 8289443
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 8289447
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasoids | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 4334146
relminmxid | 1
relacl |
reloptions |
relpartbound |
pg_classカタログに格納されているfruitsテーブルの情報を確認します。ここでは、relfilenodeの値 8289443に注目します。
pg_constraint カタログに格納されている制約情報を確認します。conrelidはこの制約が設定されているテーブルのoidが設定されます。conrelidは8289443が設定されています。contype = u は一意性制約を表しています。
=> select * from pg_constraint where conrelid = 8289443;
conname | fruits_name_key
connamespace | 16386
contype | u
condeferrable | f
condeferred | f
convalidated | t
conrelid | 8289443
contypid | 0
conindid | 8289452
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {2}
confkey |
conpfeqop |
conppeqop |
conffeqop |
conexclop |
conbin |
consrc |
また、pg_constraintカタログのconindidはこの制約で利用されるインデックスのoidが設定されます。
pg_indexカタログを確認すると対応するインデックス情報が格納されています。indisunique = t が一意性インデックスを表しています。indrelidには、このインデックスが使われるテーブルのoidが設定されるので、fruitstテーブルの8289443が設定されています。
=> select * from pg_index where indrelid = 8289443;
indexrelid | 8289452
indrelid | 8289443
indnatts | 1
indnkeyatts | 1
indisunique | t
indisprimary | f
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 2
indcollation | 100
indclass | 3126
indoption | 0
indexprs |
indpred |
検証
同じnameを設定してinsertするとエラーが発生します。正しく機能している事がわかりました。
=> insert into fruits (name) values ('apple');
INSERT 0 1
=> insert into fruits (name) values ('apple');
ERROR: duplicate key value violates unique constraint "fruits_name_key"
DETAIL: Key (name)=(apple) already exists.
UNIQUE INDEX文で検証
ALTER TABLE文で、fruitsテーブルにUNIQUE属性を追加した場合に、一意性インデックスが作成されました。一意制約はそのインデックスで実現されている事わかったので、一意インデックスだけ作成してみます。
CREATE UNIQUE INDEX unique_idx_fruits_name ON fruits (name);
=> CREATE UNIQUE INDEX unique_idx_fruits_name ON fruits (name);
CREATE INDEX
=> \d fruits
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+------------------------
id | integer | | not null | nextval('fruits_id_seq'::regclass)
name | character varying | | not null |
Indexes:
"fruits_pkey" PRIMARY KEY, btree (id)
"unique_idx_fruits_name" UNIQUE, btree (name)
unique_idx_fruits_nameが作成されています。ただ、CONSTRAINTの出力がありません。
検証
同じnameを設定してinsertするとエラーが発生します。正しい挙動はしてそうです。
=> insert into fruits (name) values ('apple');
INSERT 0 1
=> insert into fruits (name) values ('apple');
ERROR: duplicate key value violates unique constraint "unique_idx_fruits_name"
DETAIL: Key (name)=(apple) already exists.
同様にシステムカタログを確認します。
=> select * from pg_class where relname = 'fruits';
relname | fruits
relnamespace | 16386
reltype | 8289458
reloftype | 0
relowner | 16385
relam | 0
relfilenode | 8289456
reltablespace | 0
relpages | 0
reltuples | 0
relallvisible | 0
reltoastrelid | 8289460
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasoids | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 4334153
relminmxid | 1
relacl |
reloptions |
relpartbound |
=> select * from pg_constraint where conrelid = 8289456;
conname | fruits_pkey
connamespace | 16386
contype | p
condeferrable | f
condeferred | f
convalidated | t
conrelid | 8289456
contypid | 0
conindid | 8289463
conparentid | 0
confrelid | 0
confupdtype |
confdeltype |
confmatchtype |
conislocal | t
coninhcount | 0
connoinherit | t
conkey | {1}
confkey |
conpfeqop |
conppeqop |
conffeqop |
conexclop |
conbin |
consrc |
=> select * from pg_index where indrelid = 8289456;
-[ RECORD 1 ]--+--------
indexrelid | 8289465
indrelid | 8289456
indnatts | 1
indnkeyatts | 1
indisunique | t
indisprimary | f
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 2
indcollation | 100
indclass | 3126
indoption | 0
indexprs |
indpred |
-[ RECORD 2 ]--+--------
indexrelid | 8289463
indrelid | 8289456
indnatts | 1
indnkeyatts | 1
indisunique | t
indisprimary | t
indisexclusion | f
indimmediate | t
indisclustered | f
indisvalid | t
indcheckxmin | f
indisready | t
indislive | t
indisreplident | f
indkey | 1
indcollation | 0
indclass | 1978
indoption | 0
indexprs |
indpred |
unique_idx_fruits_nameは制約名ではないので、pg_constraintには格納されてません。
indrelidを指定しているので2件取得されてますが、RECORD 2の方が追加した一意インデックスです。(RECORD 1は主キーに対するインデックス)
まとめ
① ALTER TABLE fruits ADD UNIQUE(name);
② CREATE UNIQUE INDEX unique_idx_fruits_name ON fruits (name);
②の場合はpg_constraintカタログ（テーブル上の検査制約）に登録されない事がわかりましたが、①、②どちらも、ユニークインデックスが作成され、期待していた挙動の確認はできました。
モヤモヤするので、PostgreSQLのマニュアルを見ると、次のように書かれています。
インデックスは、列値の一意性や、複数列を組み合わせた値の一意性を強制するためにも使用できます。
PostgreSQLでは、テーブルに一意性制約または主キーが定義されると、自動的に一意インデックスを作成します。 このインデックスが、主キーや一意性制約（適切ならば複数列のインデックスで）となる列に対して作成され、この制約を強制する機構となります。PostgreSQL 12.4文書 第11章 インデックス 11.6. 一意インデックス 引用
また、9.4以前のマニュアルで、11.6. Unique IndexesのNoteには、
The preferred way to add a unique constraint to a table is ALTER TABLE … ADD CONSTRAINT.
という記述があり、ALTER TABLEが推奨されていたようだけど、それ以降のバージョンではその記述がありません・・・
テーブル制約として設定するカラムの一意制約と、ユニークインデックス。ムムム・・・と、なったのですがマニュアルにはこんな記述もありした。
一意性制約を追加すると、制約で指定された列または列のグループに対して一意的なBツリーのインデックスが自動的に作られます。 一部の行だけに適用される一意性の制限を一意性制約として作成することはできませんが、そのような制限を一意な部分インデックスを作成することで実現することは可能です。
一意制約（テーブル制約、ユニークインデックス）は同じ挙動。一意制約として部分インデックスを指定する場合は、テーブル制約は付けられないということでしょうかね。