このページは、下記説明のみを行い、教科書の説明を飛ばす。
■制約とは
表の中のデータに
一定の規則を設けること。
データ型だけでなく制約を定義することにより、より強力にデータのチェックを掛けることができる。■制約の種類
- エンティティ整合性制約
- 列の値が、定義した規則に沿って格納されることを保障する制約。
- 参照整合性制約
- 列の値が、参照する表の列の値であること（参照する表の列にはないデータは格納されないこと）を保障する制約。
Accessのリレーションシップと同じ。■POINT
- 整合性制約は
データ･ディクショナリに格納される。
■制約の指定方法
- 1.列制約構文
- 個々の列の定義に続けて、その列に対する制約を記述する。
《構文》
CREATE TABLE 表名 (
列名 データ型[CONSTRAINT 制約名] 列制約構文,
・・・
)
- １つの列に複数の列制約をかけるには、制約を空白文字で区切って指定する。
- 2.表制約構文
- 表の列を全て定義した後に、各制約をひとつずつ記述する。
《構文》
CREATE TABLE 表名 (
列名 データ型,
・・・,
CONSTRAINT 制約名 表制約構文,
・・・
)
- 複数の表制約をかけるには、制約をカンマで区切って指定する。
■例
id, nameの２つの列を持つテーブルhyoを作成し、idに主キー制約を付ける
列制約構文で記述 CREATE TABLE hyo (
id NUMBER(5)[CONSTRAINT pk_hyo] PRIMARY KEY,
name VARCHAR2(20)
);表制約構文で記述 CREATE TABLE hyo (
id NUMBER(5),
name VARCHAR2(20),
CONSTRAINT pk_hyo PRIMARY KEY (id)
);■POINT
- CREATE TABLE文の中に、列制約構文と表制約構文は混在して記述できる。
《列制約構文と表制約構文の混在の例》
CREATE TABLE hyo (
id NUMBER(5)[CONSTRAINT pk_hyo] PRIMARY KEY,
name VARCHAR2(20),
ten NUMBER(3),
CONSTRAINT chk_hyo_ten CHECK (ten >= 0)
);
■NOT NULL制約
列に対し、
NULL値の格納を禁止する。
この制約が定義された列には、必ずデータを入れなければならない。入れないとエラーになる。《構文》
列名 データ型 [CONSTRAINT 制約名] NOT NULL《POINT》
- NOT NULL制約は
列制約構文でしか定義できない。■一意キー制約（UNIQUE制約）
表内で、一意の値を持つ必要がある列または列の組み合わせに指定。
この制約が定義された列または列の組み合わせには、重複した値の格納を禁止する。 ただし、複数のNULL値は格納できる。《列制約構文》
列名 データ型 [CONSTRAINT 制約名] UNIQUE《表制約構文》
CONSTRAINT 制約名 UNIQUE (列名 [,列名 , ・・・])《POINT》
- 複数列をまとめて１つの一意キーにするには、
表制約構文でしか定義できない。■主キー制約
表内で、一意の値を持つ必要がある列または列の組み合わせに指定。
この制約が定義された列または列の組合わせには、重複した値およびNULL値の格納を禁止する（主キー制約 ＝ 一意キー制約 ＋ NOT NULL制約）。《列制約構文》
列名 データ型 [CONSTRAINT 制約名] PRIMARY KEY《表制約構文》
CONSTRAINT 制約名 PRIMARY KEY (列名 [,列名 , ・・・])《POINT》
- 主キー制約は
１つの表に１つしか指定できない。- 主キー制約を定義した列に、NOT NULL制約は必要ない。
■チェック制約
格納される
列の値が満たす必要のある条件を指定。データ型では対応できない値を制限できる（マイナスの値を禁止するなど）。
１つの列に複数のCHECK制約を定義できる。また、複数の列の値をチェックするCHECK制約も定義できる（表制約構文のみ）。《列制約構文》
列名 データ型 [CONSTRAINT 制約名] CHECK (条件)《表制約構文》
CONSTRAINT 制約名 CHECK (条件)《POINT》
- 条件の書き方は
WHERE句の条件と同じ。■DEFAULT
INSERTコマンドで値を省略された列は、通常NULLが格納されるが、この指定をすることにより、値が省略された場合は指定された値が格納される。
（制約ではない）《構文》
列名 データ型 DEFAULT 値《POINT》
- 制約文ではないため、
列に対してしか指定できない。■エンティティ整合性制約の使用例
《列制約構文を用いた例》
CREATE TABLE hyo ( empno NUMBER(4) CONSTRAINT pk_hyo PRIMARY KEY CONSTRAINT check_hyo_empno CHECK (empno > 0), ename VARCHAR2(10) CONSTRAINT notnull_hyo_ename NOT NULL, job VARCHAR2(9), mgr NUMBER(4), hiredate DATE DEFAULT sysdate, sal NUMBER(7,2) CONSTRAINT check_hyo_sal CHECK (sal > 0), comm NUMBER(7,2), deptno NUMBER(2) ) TABLESPACE user_data;《表制約構文を用いた例》
CREATE TABLE hyo ( empno NUMBER(4), ename VARCHAR2(10) CONSTRAINT notnull_hyo_ename NOT NULL, job VARCHAR2(9), mgr NUMBER(4), hiredate DATE DEFAULT sysdate, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), CONSTRAINT pk_hyo PRIMARY KEY (empno), CONSTRAINT check_hyo_empno CHECK (empno > 0), CONSTRAINT check_hyo_sal CHECK (sal > 0) ) TABLESPACE user_data;※NOT NULL制約およびDEFAULT句は列でしか指定できない
■参照整合性制約とは
参照整合性に対応付けられた制約。
列とその列から参照される列に格納される値が等しいことが保障される。
たとえば社員表にデータを入力する場合、部門番号は部門表に存在するデータしか入力できないように制約をかけることができる。これを参照整合性制約という。
このとき、部門表を「親表」、社員表を「子表」、社員表の部門番号を「外部キー」と呼ぶ。■構文
《列制約構文》
列名 データ型 [CONSTRAINT 制約名] REFERENCES 参照表 (参照列)《表制約構文》
CONSTRAINT 制約名 FOREIGN KEY 列名 REFERENCES 参照表 (参照列)■POINT
- 参照整合性制約で関連付けられる列は、同じデータ型で同じ長さでなければならない。
- 参照性合成制約が定義されている列でも、NULLは入力できる。NULLを不許可にするには、さらにNOT NULL制約を定義する。
■参照整合性制約の指定手順
- 親表を作成する。親キーにする列に対し、主キー制約（PRIMARY KEY）か一意キー制約（UNIQUE）を定義。
- 子表を作成する。外部キーにする列に対し、参照整合性制約を定義する。
■POINT
- 参照される表の列（親表の列）には、PRIMARY KEY整合性制約またはUNIQUE整合性制約が定義されている必要がある。
■補足：ON DELETE CASCADEオプション
参照整合性制約の設定時、
ON DELETE CASCADEを指定すると、親表の行の削除時に自動的に子表の参照行を削除する。
■制約を確認するデータ・ディクショナリの紹介
- USER_CONSTRAINTS
- 制約の名前（CONSTRAINT 制約名）と、制約の種類を確認する。
- CHECK制約は、その条件が確認できる。
- 参照整合性制約は、どの制約を参照しているかが確認できる。
- 制約の定義時、制約名を省略した場合は、Oracleによって自動的に制約名（SYS_C????）がつけられる。
USER_CONSTRAINTSの列（抜粋） OWNER 制約定義の所有者 CONSTRAINT_NAME 制約名 CONSTRAINT_TYPE 制約の種類 TABLE_NAME 表名 SEARCH_CONDITION 表の検査条件（CHECK制約） R_CONSTRAINT_NAME 参照する制約名（参照整合性制約） STATUS 制約の状態（使用可能／使用禁止） - USER_CONS_COLUMNS
- 制約名が、どの表のどの列に定義されているかを確認。
USER_CONS_COLUMNSの列（抜粋） COLUMN_NAME 列名 ■
まとめ操作例を確認すること！
■データ整合性の種類
- 1.宣言型
- 表の定義時に規則（制約）を埋め込む。
- エンティティ整合性制約
- 参照整合性制約
- 2.手続き型
- プログラムでデータをチェックする
- データベース・トリガー
- アプリケーション・コード
《POINT》
アプリケーションとデータを分離するという（リレーショナル・データベース）本来の考え方からすると、
データ整合性チェックは、全てデータベース側で行うべきである。■制約の利点
- 宣言の容易性
- 集中化された規則
- 最大のアプリケーション開発生産性
- 優れたコスト・パフォーマンス
- 制約の使用禁止／使用可能による柔軟性
■制約の有効・無効の切り替え方法
《構文》
ALTER TABLE 表名 ENABLE/DISABLE CONSTRAINT 制約名; ※ENABLEを指定すれば「有効」、DISABLEを指定すれば「無効」になる
■参照整合性制約の補足
参照整合性制約で他の表の親表となっている表は、DROP TABLEで削除することはできない。
（親表を削除すると、子表の参照整合性制約が壊れるため）《削除方法》
- 子表の参照整合性制約を削除（DROP CONSTRAINT 〜）してから、親表を削除する。
（制約は、その制約のみを削除することができる）- CASCADE CONSTRAINTオプションを指定して、親表を削除する。この場合、削除する表を親表として参照している子表の制約が全て削除される。
（削除されるものは制約であり、子表自身は残る）■POINT
- 列制約構文と表制約構文は、混在して宣言できる
- NOT NULL制約およびDEFAULTは、列制約構文でしか宣言できない
- 参照整合性制約で、参照される側の親キーは、主キーまたは一意キーでなければならない
問題１
参照整合性制約において、「親キーは必ず主キーでなければならない」は正しいかどうか答えよ。
問題２
制約について、正しくないものを選べ。
- 親表のデータは更新できない。
- 親子関係がある場合は、子表に対して親表にない参照キーを持つ行を挿入できない。
- PRIMARY KEYは、１表あたり１列にしか設定できない。
- 参照整合性制約の親キーになるには、PRIMARY KEYかUNIQUEキーである必要がある。
- NOT NULL制約は、列制約でしか設定できない。
問題３
制約名に関連付けられた列を問い合わせる場合に参照するデータ･ディクショナリはどれか答えよ。
- USER_CONSTRAINTS
- USER_CONSTRAINT
- USER_CONS_COLUMNS
- USER_CONS_COLUMN
USER_〜、ALL_〜、DBA_〜というデータ･ディクショナリには、最後に必ず「Ｓ」が付く。