CHECK 制約 と ALTER TABLE tablename ADD CONSTRAINT

ALTER TABLE tablename ADD CONSTRAINT を

新規テーブルへ実行

ALTER TABLE 可能

CREATE TABLE projects (
  projectname text NOT NULL,
  membernum integer NOT NULL
);

ALTER TABLE projects
ADD CONSTRAINT projects_membernum_check CHECK(membernum BETWEEN 1 AND 100);
=> ALTER TABLE

CONSTRAINT 制約内の集合を INSERT したテーブルへ実行

ALTER TABLE 可能

CREATE TABLE projects (
  projectname text NOT NULL,
  membernum integer NOT NULL
);
=> CREATE TABLE

INSERT INTO projects VALUES ('testproject', 100');
=> INSERT 0 1

ALTER TABLE projects
ADD CONSTRAINT projects_membernum_check CHECK(membernum BETWEEN 1 AND 100);
=> ALTER TABLE

CONSTRAINT 制約外の集合を INSERT したテーブルへ実行

制約外の集合が原因で ALTER TABLE できない

(後出しジャンケンはムリ)

CREATE TABLE projects (
  projectname text NOT NULL,
  membernum integer NOT NULL
);
=> CREATE TABLE

INSERT INTO projects VALUES ('testproject', 101');
=> INSERT 0 1

ALTER TABLE projects
ADD CONSTRAINT projects_membernum_check CHECK(membernum BETWEEN 1 AND 100);
=> ERROR:  check constraint "projects_membernum_check" is violated by some row