PostgreSQLの制約
Daily Blogging75日目
テーブルに不正な値が入らないように制約を設けよう
制約の種類
- CHECK制約
- NOT NULL制約
- UNIQUE制約
- プライマリキー制約
- 外部キー制約
- 排他制約
CHECK制約
特定の条件を満たした値だけが格納されるようにする。
-- priceは0以上であることを保証する
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
名前をつけてわかりやすくしよう
CHECK制約はCONSTRAINTを使うことで名前をつけることができる
名前をつけると２つのメリットがあるよ
- エラーがわかりやすくなる
- 制約をドロップしやすくなる
制約名をつけると、条件を満たさない値の時にこういうエラーを出してくれる
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
ERROR: new row for relation "products" violates check constraint "positive_price"
制約をドロップしたい時は、自分でつけた名前を指定することでドロップできる。
ALTER TABLE products DROP CONSTRAINT positive_price;
自分でつけなくてもシステムが自動で名前をつけてくれるが、その場合はまず制約名を確認するところから始まるよ
NOT NULL制約
Nullは不具合の元
極力nullは入れられないようにしよう
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
UNIQUE制約
値の重複を許さない
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE
);
UNIQUE制約をつけると自動でインデックスが作成されるよ
プライマリキー制約
制約をつけられた値は、そのテーブルで一意であることを示す。
自動的にNOT NULLとUNIQUE制約が付与される。
これも自動でインデックスが付与されるよ
CREATE TABLE users (
id SERIAL PRIMARY KEY
);
各テーブルで１つの列だけがプライマリキーになれる
複合主キーも作れるよ
※組み合わせがユニークである
CREATE TABLE orders (
user_id INT,
product_id INT,
PRIMARY KEY (user_id, product_id)
);
外部キー制約
他のテーブルの値との整合性を保証する
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id)
);
使えるのは他のテーブルのプライマリキー、もしくはUNIQUE制約があるもの
外部キーは主キーであるかまたは一意制約を構成する列を参照しなければなりません。これは、被参照列は常に(主キーまたは一意制約の基礎となる)インデックスを持つことを意味します;このため、参照行が一致するかのチェックは効率的です
排他制約
指定した条件に基づいてデータの重複を防ぐよ
room_idが同じデータで、star_atとend_atの期間が重複するデータが格納されないようにする
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INT,
start_time TIMESTAMP,
end_time TIMESTAMP,
EXCLUDE USING GIST (
room_id WITH =,
tstzrange(start_time, end_time) WITH &&
)
);
USING GISTは、GISTインデックスの使用を表す
→範囲など複雑な検索の時に有効らしい
EXCLUDE USING GISTと記述することでインデックスも作られる。
tstzrangeは範囲型
範囲型の&&は重複を指す
Discussion