制約

各カラムにはCHECKUNIQUEPRIMARY KEYREFERENCESなどの制約を設定できます。

CHECK

CHECK (condition)のように書け、conditiontrueなデータだけ登録するように制限できます。

以下は、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を置いて設定します。これを設定したカラムの値は全レコード全てで異なる値でなければなれないという制限ができます。

以下は、number1を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

参照テーブルにid1のレコードを入れたので、先程エラーになったものをもう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に更新される)