整合性制約は、“テーブルにデータを入れる条件”を設定するもの。
この条件を満たさないデータは入れることが出来ない。(入れようとした時点でエラーになる)
これにより、テーブルの整合性を保つことが出来る(不整合にならない)。
不正なデータを事前にはじくという考えは、アサート(アサーション)みたいなものかな。
制約には色々な種類があるが、制約を定義・変更するには全般的に以下のような構文を使う。
(ALTER TABLEだから、テーブルの属性を定義するようなイメージ)
操作 | 構文 | 備考 |
---|---|---|
表内指定 | CREATE TABLE テーブル名 |
列定義の一部として定義する(inline_constraint)。CREATE TABLE TEST (CODE CHAR(4) CONSTRAINT PK_TEST PRIMARY KEY, DATA1 CHAR(8) CONSTRAINT NN_DATA1 NOT NULL CONSTRAINT UQ_DATA1 UNIQUE, DATA2 NUMBER(8) ); |
表外指定 | CREATE TABLE テーブル名 |
表定義の一部として定義する(out_of_line_constraint)。 この定義方法では項目定義と項目定義の間に書くことも可能だが、大抵は最後にまとめて書く。 CREATE TABLE TEST (CODE CHAR(4), DATA1 CHAR(8) NOT NULL, DATA2 NUMBER(8), CONSTRAINT PK_TEST PRIMARY KEY(CODE), CONSTRAINT UQ_DATA1 UNIQUE(DATA1) ); |
制約の追加 | ALTER TABLE テーブル名 |
制約名は省略可能。 |
制約の削除 | ALTER TABLE テーブル名 |
|
制約の変更 | ALTER TABLE テーブル名 |
|
制約名の変更 | ALTER TABLE テーブル名 |
→プライマリキー制約名の変更方法 |
制約の無効化 | ALTER TABLE テーブル名 |
制約の定義自体は削除せずにその制約の条件判断の実行有無を設定する。デフォルトでは「有効」。 無効にしている最中に制約違反のデータを入れることが出来るが、有効に戻そうとした際にエラーになって有効に出来ない。 →無効化するとINSERT効率が良くなる場合がある |
制約の有効化 | ALTER TABLE テーブル名 |
定義する際に制約名を省略することが出来る。
その場合でも“SYS_なんちゃら
”みたいな名前が適当に付けられているので、制約を変更したい場合にはそれを使うことが出来る。
プライマリキー。最も有名(代表的)な制約。1つのテーブルに1つだけしか持てない。
論理的にはユニークキー制約とNOT NULL制約を併せ持つ。
CREATE TABLE テーブル名 (項目名 属性, 項目名 属性, …, 〔CONSTRAINT 主キー名〕 PRIMARY KEY (項目名, 項目名, …) );
CREATE TABLE テーブル名 (項目名 属性 〔CONSTRAINT 主キー名〕 PRIMARY KEY, --この方法は、主キーが一項目だけの場合のみ使用可能 項目名 属性, … );
ALTER TABLE テーブル名 ADD 〔CONSTRAINT 主キー名〕 PRIMARY KEY(項目名, 項目名, …) ;
プライマリキーを追加すると、それらの項目は自動的にNOT NULLになる。[2010-02-19]
通常の制約の削除方法の他に、以下のような方法でも削除可能。
ALTER TABLE テーブル名 DROP PRIMARY KEY ;
プライマリキーは(ユニークキーも)、他のテーブルからの参照整合性制約(外部キー)に使われる可能性がある。
それに使われている場合、主キー制約を削除することは出来ない。
SQL> alter table dept
2 drop constraint PK_DEPT;
drop constraint PK_DEPT
*
行2でエラーが発生しました。:
ORA-02273: この一意/主キーは外部キーによって参照されています。
→プライマリキーがどのテーブルから使われているか確認する方法
プライマリキー制約の名前を変えたい場合、制約の名前とインデックスの名前を変更する必要がある。[2010-02-19]
→制約名の変更方法
→インデックス名の変更方法
ユニークキー(一意キー)。同じ値を持つ行(レコード)を許さない。
一意キー制約を作成すると、暗黙に索引(インデックス)が作られる。その際、インデックス名は制約名と同じになる。
CREATE TABLE テーブル名 (項目名 属性 〔CONSTRAINT 制約名〕 UNIQUE, 項目名 属性, … );
複数の列(項目)毎に一意にする制約は複合一意キーと呼ばれる。
CREATE TABLE テーブル名 (項目名 属性, 項目名 属性, …, 〔CONSTRAINT 制約名〕 UNIQUE (項目名, 項目名, …) );
ALTER TABLE テーブル名 ADD 〔CONSTRAINT 制約名〕 UNIQUE(項目名, 項目名, …) ;
通常の制約の削除方法の他に、以下のような方法でも削除可能。
ALTER TABLE テーブル名 DROP UNIQUE(項目名, 項目名, …) ;
複合一意キーを削除する場合、定義したときと同じ項目の並び順でないと削除できない。
また、複数項目の内の一項目だけを削除することも出来ない。
SQL> alter table test 2 drop unique(data1); drop unique(data1) * 行2でエラーが発生しました。: ORA-02442: 存在しない一意キーを削除することはできません。 SQL> alter table test 2 drop unique(data2,data1); drop unique(data2,data1) * 行2でエラーが発生しました。: ORA-02442: 存在しない一意キーを削除することはできません。 SQL> alter table test 2 drop unique(data1,data2); 表が変更されました。
指定された条件を満たすデータだけをテーブルに入れられるようにする。
WHERE句を指定するような感じ。
CREATE TABLE テーブル名 (項目名 属性 〔CONSTRAINT 制約名〕 CHECK(正当条件), --自分の項目に関する条件しか書けない 項目名 属性, … );
CREATE TABLE テーブル名 (項目名 属性, 項目名 属性, …, 〔CONSTRAINT 制約名〕 CHECK(正当条件) );
実際には「CHECK(項目 > 0)
」とか「CHECK(項目A > 0 AND 項目B > 0)
」という感じ。
ALTER TABLE テーブル名 ADD 〔CONSTRAINT 制約名〕 CHECK(正当条件) ;
さすがに通常の制約の削除以外の方法は無さそう。
データとしてNULLを許さない。
この制約の定義方法は独立した文法になっているが、実体としてはCHECK制約で「項目 IS NOT
NULL」を定義したのと同じ形になる模様。
CREATE TABLE テーブル名 (項目名 属性 〔CONSTRAINT 制約名〕 NOT NULL, 項目名 属性, … );
逆にNULLを許すことを明示的に示したい場合は、「NOT NULL」でなく「NULL」を指定する。
この際も制約名を指定することは出来るが、制約としては何も作られないようだ。
CREATE TABLE テーブル名 (項目名 属性 〔CONSTRAINT 制約名〕 NULL, 項目名 属性, … );
NOT NULLは(実体としてはCHECK制約なので、)ALTER TABLE ADDでは追加できない。
項目の属性に対する変更という形をとる。
ALTER TABLE テーブル名 MODIFY (項目名 〔CONSTRAINT 制約名〕 NOT NULL) ;
ALTER TABLE テーブル名 MODIFY 項目名 〔CONSTRAINT 制約名〕 NOT NULL ;
追加の場合と同様に、MODIFYでNULLに変更する形をとる。
ALTER TABLE テーブル名 MODIFY (項目名 〔CONSTRAINT 制約名〕 NULL) ;
NOT NULL制約が付いていると、SQL*Plusのdescコマンドでテーブル定義を見たときに「NULL?」という列に「NOT NULL」と表示される。
しかし実体として同じだからということで「項目 IS NOT NULL」というCHECK制約を定義してみると、動作は意図した通りになるが、descコマンドで見たときにNOT NULL扱いにはならない。
SQL> create table test(data char(4)); 表が作成されました。 SQL> alter table test add check("DATA" IS NOT NULL); 表が変更されました。 SQL> insert into test values(null); insert into test values(null) * 行1でエラーが発生しました。: ORA-02290: チェック制約(SCOTT.SYS_C003034)に違反しました SQL> desc test 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- DATA CHAR(4)
外部キー(フォーリンキー)(参照整合性制約)。テーブルの値が、別のテーブルに存在している値であることを保証する。
この制約を付けるとデータを作成する順序(どのテーブルから先にデータを入れないといけないか)を考えなければならないし、テストデータを作る際に 実際に使いたいのとは無関係なテーブルにもデータを入れる必要が出てきたりするので、あえて外部キー制約を付けないことも多い(それで良いのか悪いのかは別として)。
外部キー制約は、目的の外部テーブルのユニークキー(普通はプライマリキー)に対してしか指定できない。
たまに「コード」「開始日」「終了日」をキーに持ち、「(コード=値) AND (当日 BETWEEN 開始日 AND
終了日)」で一意になるような意図のテーブル(コードに対する有効期間・履歴を持つ)が設計されるが、この場合、ユニークキーが「コード」と「開始日」なら 「コード」のみで外部キー制約とすることは出来ない。
「開始日」まで含むとしても、「コード」と「開始日」そのものがぴったり一致する制約ということになってしまうので、たぶん望んだ結果ではないだろう。
CREATE TABLE テーブル名 (項目名 属性, 項目名 属性, …, 〔CONSTRAINT 制約名〕 FOREIGN KEY(項目名, 項目名, …) REFERENCES 外部テーブル名(一意キー, 一意キー, …) );
ALTER TABLE テーブル名 ADD 〔CONSTRAINT 制約名〕 FOREIGN KEY(項目名, 項目名, …) REFERENCES 外部テーブル名(一意キー, 一意キー, …) ;
通常の制約の削除方法で行う。
あるテーブルにどんな制約が存在しているかは、user_constraintsビューを使って確認できる。
select constraint_name,constraint_type from user_constraints where table_name = 'テーブル名' ;
SQL> select constraint_name,constraint_type
2 from user_constraints
3 where table_name = 'EMP'
4 ;
CONSTRAINT_NAME C
---------------- -
PK_EMP P
FK_DEPTNO R
TYPE | 制約 | 備考 | |
---|---|---|---|
P | 主キー制約(プライマリキー制約) | primary key | |
U | 一意制約(ユニークキー制約) | unique | |
C | CHECK制約(チェック制約) | check | NOT NULL制約もこれになる |
R | 外部キー制約(参照整合性制約) | references |
制約のついている項目名まで照会するには、user_cons_columnsビューも使用する。
(テーブル名や制約名は長いので、SQL*PlusのCOLUMNコマンドを使って桁数制限をしておくと見やすくなる)
col table_name format a16 col constraint_name format a16 col column_name format a16
select c.table_name, c.constraint_name, c.constraint_type, cc.position, cc.column_name from user_constraints c, user_cons_columns cc where c.table_name = cc.table_name and c.constraint_name = cc.constraint_name and c.table_name = 'テーブル名' order by cc.table_name, cc.constraint_name, cc.position ;
SQL> select
2 c.table_name, c.constraint_name, c.constraint_type,
3 cc.position, cc.column_name
4 from user_constraints c, user_cons_columns cc
5 where c.table_name = cc.table_name
6 and c.constraint_name = cc.constraint_name
7 and c.table_name = 'EMP'
8 order by cc.table_name, cc.constraint_name, cc.position
9 ;
TABLE_NAME CONSTRAINT_NAME C POSITION COLUMN_NAME
---------------- ---------------- - ---------- ----------------
EMP FK_DEPTNO R 1 DEPTNO
EMP PK_EMP P 1 EMPNO
一つの制約が複数の項目にまたがっている場合、POSITIONが1,2,3…となって全項目が表示される。
(プライマリキーやユニークキーは複数の項目を指定できる)
CHECK制約でも、複数項目にまたがった条件を書いた場合、その中に出てくる項目がここに表示される。
CHECK制約の内容を確認するには、SEARCH_CONDITIONという項目を参照すればいい。
select SEARCH_CONDITION from user_constraints where constraint_name = '制約名' ;
SQL> create table test 2 (CODE char(4), 3 NUM number(4), 4 constraint CK_TEST1 check(to_number(CODE) + NUM >= 0) 5 ); 表が作成されました。 SQL> select SEARCH_CONDITION from user_constraints 2 where constraint_name='CK_TEST1' 3 ; SEARCH_CONDITION -------------------------------------------------------------------------------- to_number(CODE) + NUM >= 0 SQL> select column_name,position 2 from user_cons_columns 3 where constraint_name='CK_TEST1' 4 ; COLUMN_NAME POSITION ---------------- ---------- CODE NUM
自分のユニークキー制約(あるいはプライマリキー制約)が 他のテーブルの外部キー制約として使われているかどうかを調べるには、以下のような条件で検索する。
select table_name,constraint_name from user_constraints where R_CONSTRAINT_NAME = '制約名' ;
例えばPK_DEPT(DEPTテーブルの主キー制約)がどのテーブルから参照されているかを確認するのにこのSQLを実行する。
SQL> select table_name,constraint_name 2 from user_constraints 3 where r_constraint_name='PK_DEPT' 4 ; TABLE_NAME CONSTRAINT_NAME ---------------- ---------------- EMP FK_DEPTNO
EMPテーブルがFK_DEPTNOという外部キー制約をDEPTテーブル(のPK_DEPTという主キー制約)に対して持っていることが分かる。