各カラムにはCHECKやUNIQUE、PRIMARY KEY、REFERENCESなどの制約を設定できます。
CHECK
CHECK (condition)のように書け、conditionがtrueなデータだけ登録するように制限できます。
以下は、numberへは1以上の値しか入れれないという制限を設定してます。もしその条件を満たさないものを入れようとするとエラーが起こります。
CREATE TABLE example_check (
number INTEGER CHECK ( number > 0 )
);
-- CREATE TABLE
INSERT INTO example_check (number)
VALUES ( -1 );
-- ERROR: new row for relation "example_check" violates check constraint "example_check_number_check"
エラー文にある通りこの制約名はデフォルトで<table_name>_<column_name>_checkです。
UNIQUE
単にUNIEUQを置いて設定します。これを設定したカラムの値は全レコード全てで異なる値でなければなれないという制限ができます。
以下は、numberへ1を2回入れようとしたためエラーが起きています。
CREATE TABLE example_unique (
number INTEGER UNIQUE
);
-- CREATE TABLE
INSERT INTO
example_unique ( number )
VALUES
( 1 ), ( 1 );
-- ERROR: duplicate key value violates unique constraint "example_unique_number_key"
エラー文にある通りこの制約名はデフォルトで<table_name>_<column_name>_keyです。
PRIMARY KEY
単にUNIEUQを置いて設定します。そのテーブルでの主キーを設定できます。こちらもユニークと同じようにユニークである必要があります。
CREATE TABLE example_primary_key (
id INTEGER PRIMARY KEY
);
-- CREATE TABLE
INSERT INTO
example_unique ( number )
VALUES
( 1 ), ( 1 );
-- ERROR: duplicate key value violates unique constraint "example_primary_key_pkey"
エラー文にある通りこの制約名はデフォルトで<table_name>_pkeyです。
REFERENCES
REFERENCES table(column, ...)のように書き、外部テーブルとの関連した値を安全に記録する為に使います。制約名はデフォルトで<table_name>_<column_name>_fkeyです。
例を見る前に以下で、外部テーブルとして使うテーブルを作っておきます。
CREATE TABLE example_references_parent (
id SERIAL PRIMARY KEY
);
それを使うテーブルを以下のように作ります。
CREATE TABLE example_references1 (
parent_id INTEGER REFERENCES example_references_parent(id)
);
まだexample_references_parentにはレコードは1つもありません。その状態でexample_references1へ適当なparent_id指定で入れようとしても、
INSERT INTO
example_references1 (parent_id)
VALUES
( 1 );
-- ERROR: insert or update on table "example_references1" violates foreign key constraint "example_references1_parent_id_fkey"
エラーになります。これはparent_idへ入れられる値は、example_references_parentに実在するあるid値である必要がある為です。
このエラーは先にそのような値を入れておくことで回避できます。
INSERT INTO example_references_parent DEFAULT VALUES;
-- INSERT 0 1
参照テーブルにidが1のレコードを入れたので、先程エラーになったものをもう1度試してみます。
INSERT INTO
example_references1 (parent_id)
VALUES
( 1 );
-- INSERT 0 1
今度は大丈夫でした！
そしてこの状態で参照側のレコードを消してみます。
DELETE FROM example_references_parent;
-- ERROR: update or delete on table "example_references_parent" violates foreign key constraint "example_references1_parent_id_fkey" on table "example_references1"
が、この制約がちゃんと付いてると参照されているレコードは上記のようなエラーがでて消すことができなくなります。これを回避するには先に参照しているレコードを消す必要があります。
DELETE FROM example_references1;
-- DELETE 1
DELETE FROM example_references_parent;
-- DELETE 1
もし、参照してる値がとても多い場合これでは不便かもしれません。上記の削除の動作を1度に行える方法があります。それは制約を設定する時にON UPDATE CASCADE ON DELETE CASCADEを付けます。
CREATE TABLE example_references2 (
parent_id INTEGER REFERENCES example_references_parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
-- CREATE TABLE
先程消してしまった参照テーブルと今作ったテーブルに値を入れておきます。
INSERT INTO example_references_parent DEFAULT VALUES;
-- `id: 2`のレコードが入る
INSERT INTO
example_references2 (parent_id)
VALUES
( 2 );
-- INSERT 0 1
この状態だと参照元データを削除できます。
DELETE FROM example_references_parent;
-- DELETE 1
削除してみます。
SELECT * FROM example_references2 \gx
-- (0 rows)
参照してたレコードも削除されました！これはON DELTE CASCADEによって、参照元が削除された時にそれを参照しているレコードも削除するように設定した為です。
そういえば、ON UPDATE CASCADEも一緒に設定してました。これは参照元の値が更新された時に、それを参照しているレコードの値も更新されます。（例えば親がid:1で子がparent_id:1の時、id:2へ更新すると連藤してparent_id:2に更新される）
