S-JIS[2008-02-16/2010-02-19] 変更履歴

Oracle 整合性制約

整合性制約は、“テーブルにデータを入れる条件”を設定するもの。
この条件を満たさないデータは入れることが出来ない。(入れようとした時点でエラーになる)
これにより、テーブルの整合性を保つことが出来る(不整合にならない)。

不正なデータを事前にはじくという考えは、アサート(アサーション)みたいなものかな。


制約の定義・変更方法

制約には色々な種類があるが、制約を定義・変更するには全般的に以下のような構文を使う。
(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 テーブル名
ADD 〔CONSTRAINT 制約名〕
制約定義〜
制約名は省略可能。
制約の削除 ALTER TABLE テーブル名
DROP CONSTRAINT 制約名
 
制約の変更 ALTER TABLE テーブル名
MODIFY CONSTRAINT 制約名
制約定義〜
 
制約名の変更 ALTER TABLE テーブル名
RENAME CONSTRAINT 制約名
TO 新制約名
プライマリキー制約名の変更方法
制約の無効化 ALTER TABLE テーブル名
DISABLE CONSTRAINT 制約名
制約の定義自体は削除せずにその制約の条件判断の実行有無を設定する。デフォルトでは「有効」。
無効にしている最中に制約違反のデータを入れることが出来るが、有効に戻そうとした際にエラーになって有効に出来ない。
無効化するとINSERT効率が良くなる場合がある
制約の有効化 ALTER TABLE テーブル名
ENABLE CONSTRAINT 制約名

定義する際に制約名を省略することが出来る。
その場合でも“SYS_なんちゃら”みたいな名前が適当に付けられているので、制約を変更したい場合にはそれを使うことが出来る。

制約名の確認方法


主キー制約

プライマリキー。最も有名(代表的)な制約。1つのテーブルに1つだけしか持てない。
論理的にはユニークキー制約NOT NULL制約を併せ持つ。

CREATE文の中で定義する方法

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文の中で定義する方法

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);

表が変更されました。

CHECK制約

指定された条件を満たすデータだけをテーブルに入れられるようにする。
WHERE句を指定するような感じ。

CREATE文の中で定義する方法

CREATE TABLE テーブル名
(項目名 属性 〔CONSTRAINT 制約名〕 CHECK(正当条件),	--自分の項目に関する条件しか書けない
 項目名 属性,
 …
);
CREATE TABLE テーブル名
(項目名 属性,
 項目名 属性,
 …,
〔CONSTRAINT 制約名〕 CHECK(正当条件)
);

実際には「CHECK(項目 > 0)」とか「CHECK(項目A > 0 AND 項目B > 0)」という感じ。

後から追加する方法

ALTER TABLE テーブル名
ADD 〔CONSTRAINT 制約名〕
CHECK(正当条件)
;

削除する方法

さすがに通常の制約の削除以外の方法は無さそう。

CHECK制約の内容を確認する方法


NOT NULL制約

データとしてNULLを許さない。
この制約の定義方法は独立した文法になっているが、実体としてはCHECK制約で「項目 IS NOT NULL」を定義したのと同じ形になる模様。

CREATE文の中で定義する方法

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文の中で定義する方法

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
CONSTRAINT_TYPEの値
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という主キー制約)に対して持っていることが分かる。


SQLメモへ戻る / 情報照会へ戻る / Oracle目次へ戻る / 技術メモへ戻る
メールの送信先:ひしだま