CHECK 制約

「アプリケーション層の validation は、不正値入力の『通知』に留め」

「データの整合性 はデータ層が担保しよう」

って Dan Chak さんが言ってた(覚えがあるので要復習)

---

CREATE TABLE users (
  name text NOT NULL,
  receivemailflag INTEGER NOT NULL DEFAULT 1,
  CHECK (receivemailflag IN (0, 1))
);

INSERT INTO users VALUES ('yamada', 0);
INSERT 0 1

INSERT INTO users VALUES ('shimano', 0);
INSERT 0 1

INSERT INTO users VALUES ('whoareyou', -1);
ERROR:  new row for relation "users" violates check constraint "users_receivemailflag_check"
DETAIL:  Failing row contains (whoareyou, -1).

INSERT INTO users VALUES ('whoa!', 2);
ERROR:  new row for relation "users" violates check constraint "users_receivemailflag_check"
DETAIL:  Failing row contains (whoa!, 2).

INSERT INTO users values ('noo', null);
ERROR:  null value in column "receivemailflag" violates not-null constraint
DETAIL:  Failing row contains (noo, null).

SELECT * FROM users;
  name   | receivemailflag 
---------+-----------------
 yamada  |               0
 shimano |               1
(2 rows)