データベース PostgreSQL 制約

フィールドの制約

  • NOT NULL制約

  • CHECK制約

  • UNIQUE制約

  • PRIMARY KEY制約

  • 外部キー制約


    • NOT NULL制約
    指定したフィールドにNULLを設定することを禁止する。

    CREATE TABLE mytable (
  id serial, 
  name varchar(256) <b>NOT NULL</b>
);


    CHECK制約
    制約をフィールドの値に対する任意の式で指定する。
    フィールド制約としてもテーブル制約としても記述可能。

    CREATE TABLE mytable (
  id serial PRIMARY KEY,
  age int CHECK(age > 0),
  sex int NOT NULL CHECK(sex=1 OR sex=2)
);


    UNIQUE制約
    指定したフィールドで値が同じレコードがあってはいけないという制約。
    フィールド制約としてもテーブル制約としても記述可能。
    値がNULLのフィールドはUNIQUE制約の対象にならない。複数レコードにNULLがあっても制約違反にはならない。
    PostgreSQLではUNIQUE制約はインデックスを作るという方法で実現されている。

    CREATE TABLE mytable(
  id int id UNIQUE,
  name, varchar(256) 
);
    CREATE TABLE mytable (
  id int UNIQUE,
  host_id int,
  user_id int,
  UNIQUE(host_id, user_id)
);


    PRIMARY KEY制約
    テーブルの主キーを指定する制約。

    [PRIMARY KEY制約 = NOT NULL制約 + UNIQUE制約]

    PRIMARY KEY制約はテーブルに１つしか設定できない。

    CREATE TABLE mytable (
  id serial PRIMARY KEY,
  name varchar(256)
);
    CREATE TABLE mytable (
  user_id int,
  host_id int,
  name varchar(256),
  PRIMARY KEY(user_id, host_id)
);


    外部キー制約
    外部キーを定義するには、CREATE文のフィールド定義で、referencesオプションをつける。

    [user_id int REFERENCES users(id)]
    user_idフィールドは、usersテーブルのidフィールドを参照している。

    参照先フィールド（この場合、usersテーブルのidフィールド）は、PRIMARY KEYであるかUNIQUE制約を設定する必要がある。

    CREATE TABLE users (
  id serial PRIMARY KEY,
  name varchar(256)
);
CREATE TABLE cars (
  id serial,
  user_id int REFERENCES users(id)
);

    参照されているテーブルの対象フィールドを更新、削除など変更すると参照制約によりエラーになる。
    外部キー制約にON UPDATE, ON DELETEオプションを指定して、この動作を変更できる。

    NO ACTION既定の動作。制約違反としてエラーになる
    RESTRICTNO ACTIONと同じだが、検査の遅延ができない
    CASCADEUPDATEに対して参照しているフィールドもあわせて更新する。DELETEに対して参照しているレコードを削除する
    SET NULL参照しているフィールドをNULLに設定する
    SET DEFAULT参照しているフィールドをその既定値に設定する 

    CREATE TABLE users (
  id serial PRIMARY KEY,
  name varchar(256)
);
CREATE TABLE cars (
  id serial,
  user_id int REFERENCES users(id) <b>ON UPDATE CASCADE ON DELETE SET NULL</b>
);


    外部キー制約の検査を遅らせる
    外部キーが張られているとテーブルにレコードを加えていくときに、どうしても一時的に制約違反の状態になってしまい、それを回避するのが難しいケースがある。

    PostgreSQLでは、「SET CONSTRAINTS ALL DEFERRED」というコマンドを使うことで、外部キー制約に限ってトランザクションの終わりまで制約の検査を遅延させることができる。


    フィールドの既定値

    CREATE TABLE mytable(
  id int PRIMARY KEY, 
  time timestamp DEFAULT CURRENT_TIMESTAMP
);

    シーケンス
    既定値として自動生成された連番を使う場合は、シーケンスを使う。
    シーケンスは、データベースオブジェクトの１つで「CREATE SEQUENCE」文で生成する。

    CREATE SEQUENCE seq_name;
    CREATE SEQUENCE seq;
CREATE TABLE mytable (id int DEFAULT nextval('seq'), name varchar(256));
INSERT INTO mytable(name) Values('name1');
SELECT * FROM mytable;


    インデックス

    インデックスはデータベースオブジェクトの１つ。
    インデックスメソッドによって、どの比較演算子に対してインデックスが使われうるかが決まる。btreeインデックスでは、=, < <=, >, >= の演算子に対してインデックスが適用される。

    CREATE INDEX idx_name 
ON table_name (field_name, [field_name]...) 
[USING method]
[WHERE condition]
    DROP INDEX index_name [CASCADE]