SQLの整合性制約 (CONSTRAINT)

SQLの整合性制約 (CONSTRAINT)とは、データの整合性を担保するために、表の列に入れられる値を制限する規則です。データを挿入または更新するときに整合性制約がチェックされ、違反する場合はエラーが発生します。

整合性制約の種類
1. NOT NULL
2. PRIMARY KEY
3. UNIQUE
4. FOREIGN KEY
5. CHECK
整合性制約に関するSQL文
1. CREATE TABLE
2. ALTER TABLE
  1. 表の制約を無効化する
  2. 表の制約を有効化する
  3. 表に制約を追加する
  4. 表から制約を削除する
3. CREATE VIEW
4. ALTER VIEW
整合性制約に関するデータ・ディクショナリ・ビュー
1. ALL_CNSTRAINTS
2. DBA_CONSTRAINTS
3. USER_CONSTRAINTS

整合性制約の種類

整合性制約には以下に示す種類がある。

NOT NULL
PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK

NOT NULL

NOT NULL 制約が付けられた列は、必ず値を挿入しなければならない（NULLにできない）。

SQL> CREATE TABLE dept (
  deptno NUMBER(2) PRIMARY KEY,
  dname VARCHAR2(14) NOT NULL,
  loc VARCHAR2(13)
);
SQL> INSERT INTO dept (deptno, dname) VALUES(10, null);
INSERT INTO dept (deptno, dname) VALUES(10, null)
*
行1でエラーが発生しました。:
ORA-01400: ("TSUKA"."DEPTNO"."DNAME")にはNULLは挿入できません。

PRIMARY KEY

主キー制約。NOT NULLとUNIQUEの両方の制約が付く。また、インデックスが自動的に作成される。

CREATE TABLE dept (
  deptno NUMBER(2) PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

PRIMARY KEY制約が付けられた列には、必ず値を挿入しなければならない（NULLにできない）。また、PRIMARY KEY制約が付けられた列は値が一意でなければならず、重複した値を挿入できない。つまり、NOT NULL制約とUNIQUE制約の両方を指定したことになる。

CREATE TABLE dept (
  deptno NUMBER(2) PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

UNIQUE

一意制約。この制約が付けられた列は値が一意でなければならず、重複した値を挿入できない。

FOREIGN KEY

外部キー制約。他のテーブル等に存在する値しか挿入できない。

FOREIGN KEY制約が付けられた列には、指定した表の列に存在する値しか入れることができない。これは外部キー制約と呼ばれる。

CREATE TABLE emp (
  empno NUMBER(4) PRIMARY KEY,
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr NUMBER(4),
  hiredate DATE,
  sal NUMBER(7,2),
  comm NUMBER(7,2),
  deptno NUMBER(2),
  FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE
);

CHECK

チェック制約。任意のチェック条件を指定できる。

CHECK制約を使うと、任意の条件式を指定できる。

CREATE TABLE emp (
  empno NUMBER(4) PRIMARY KEY,
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr NUMBER(4),
  hiredate DATE,
  sal NUMBER(7,2),
  comm NUMBER(7,2),
  age NUMBER(2),
  deptno NUMBER(2),
  CONSTRAINT agecheck CHECK(age BETWEEN 18 AND 65)
);

CHECK制約に違反する行を挿入または更新しようとすると、エラーが発生する。

行1でエラーが発生しました。:
ORA-02290: チェック制約(XXXX.AGECHECK)に違反しました

整合性制約に関するSQL文

整合性制約を操作するSQL文の一覧を次に示す。

CREATE TABLE

CREATE TABLE は、テーブル（表）を作成する SQL 文である。

ALTER TABLE

ALTER TABLE は、テーブル（表）を変更する。 SQL 文である。

表の制約を無効化する

表の制約を一時的に無効化できる。

ALTER TABLE [schema.]table DISABLE CONSTRAINT constraint

schema: 表が存在するスキーマの名前
table: 制約を無効化する表の名前
constraint: 無効化する制約の名前

表の制約を無効化する例を次に示す。

ALTER TABLE emp DISABLE CONSTRAINT agecheck;

表の制約を有効化する

無効化した制約を有効化する。

ALTER TABLE [schema.]table ENABLE CONSTRAINT constraint

schema: 表が存在するスキーマの名前
table: 制約を有効化する表の名前
constraint: 有効化する制約の名前

表の制約を有効化する例を次に示す。

ALTER TABLE emp ENABLE CONSTRAINT agecheck;

表に制約を追加する

既存の表に制約を追加できる。

ALTER TABLE [schema.]table ADD CONSTRAINT constraint PRIMARY KEY (columns)

ALTER TABLE [schema.]table ADD CONSTRAINT constraint UNIQUE (columns)

ALTER TABLE [schema.]table ADD CONSTRAINT constraint FOREIGN KEY (column) REFERENCES reftable(column)

ALTER TABLE [schema.]table ADD CONSTRAINT constraint CHECK (condition)

schema: 表が存在するスキーマの名前
table: 制約を追加する表の名前
constraint: 追加する制約の名前

既存の表にチェック制約を追加する例を次に示す。

ALTER TABLE emp ADD CONSTRAINT agecheck (age BETWEEN 18 AND 65);

表から制約を削除する

既存の表から制約を削除できる。

ALTER TABLE [schema.]table DROP COSTRAINT constraint

schema: 表が存在するスキーマの名前
table: 制約を削除する表の名前
constraint: 削除する制約の名前

既存の表からCHECK制約を削除する例を次に示す。

ALTER TABLE emp DROP CONSTRAINT agecheck;

CREATE VIEW

CREATE VIEW は、ビューを作成する SQL 文である。

ALTER VIEW

ALTER VIEW は、ビューを変更する SQL 文である。

整合性制約に関するデータ・ディクショナリ・ビュー

整合性制約に関する情報は、以下に示すデータ・ディクショナリ・ビューで参照できる。

ALL_CONSTRAINTS
DBA_CONSTRAINTS
USER_CONSTRAINTS

ALL_CONSTRAINTS

ALL_CONSTRAINTS は、現在のユーザがアクセスできる整合性制約を示すデータ・ディクショナリ・ビューである。

DBA_CONSTRAINTS

DBA_CONSTRAINTS は、Oracleデータベース内すべての整合性制約を示すデータ・ディクショナリ・ビューである。

USER_CONSTRAINTS

USER_CONSTRAINTS は、現在のユーザが所有する整合性制約を示すデータ・ディクショナリ・ビューである。