参照整合性制約

整合性制約のタイプ

制約の中では、もっとも面倒なものではありますが RDB 的に考えれば、なるほど・・・というものでもあります。最初につまづくのは用語の部分では無いでしょうか。とにかく、二つの表の関係が基本ですから ( 自己参照型整合性制約というのもありますが )　実際に表を作って試すのか最良の手段です。

CREATE TABLE 店頭商品マスタ (
コード NUMBER(4,0)
,商品名 VARCHAR2(50)
,単価 NUMBER(7,0)
,商品分類 NUMBER(2,0)
,PRIMARY KEY (コード)
)

	列名	型	データ長	精度	小数以下	NULL可
1	コード	NUMBER	22	4	0	N
2	商品名	VARCHAR2	50			Y
3	単価	NUMBER	22	7	0	Y
4	商品分類	NUMBER	22	2	0	Y

CREATE TABLE 商品分類マスタ (
コード NUMBER(2,0)
,名称 VARCHAR2(20)
,PRIMARY KEY (コード)
)

	列名	型	データ長	精度	小数以下	NULL可	ﾃﾞﾌｫﾙﾄ値
1	コード	NUMBER	22	2	0	N
2	名称	VARCHAR2	20			Y

参照整合性制約を作る

4つの用語を確認しておきます

外部キー
参照キー
子表 ( 依存表 )
親表 ( 参照表 )

まず外部キーですが、これが定義のもととなるもので、ここでは店頭商品マスタ.商品分類です。定義時には、FOREIGN KEY キーワードの後に来る列名リストとして示されます

参照キーも、定義時に REFERENCES キーワードの後に来る列名リストとして示されます。つまりこれらの用語は定義構文と１対１に対応しています。構文は結果的に以下のようになります

ALTER TABLE 店頭商品マスタ
ADD
	constraint 制約名_店頭商品マスタ参照
	foreign key (商品分類)
	references 商品分類マスタ(コード)

もともと目的は、「参照」なので、その言葉を中心に考えれば、商品分類マスタが「参照表」であり親表です。また、店頭商品マスタが「依存表」であり子表である事になります

制限

制限はいろいろありますが、まず「親表ありき」です。オブジェクト的に考えても、無いものは指定できませんが、子は親から生まれます。親が無ければ親子関係は作成できません。

次に、「参照」の意味からして、「参照キー」はユニークである必要があります。Oracle 的に言えば、PRIMARY KEY か、UNIQUE制約を持つ列でなければなりません。

そして、いったん参照してしまったら親表はもとより、参照している親表のデータも削除できません。これは、システム的に考えれば当然の事で、データの欠落 = システム稼動不能となってしまうからです。そもそも、こういう問題を避ける為の制約なのです。

データ的に言えば、親に無いデータは作成できません。唯一例外は NULL です。これは、運用上の逃げ道として許されています。親データを決定する前に子データを作成しなければならない事は良くある事なのです。しかし、設計上これを許すのも避けたい場合は、外部キーに NOT NULL 制約を指定する事で実現できます。

オプション

どういう場合にこれを指定するかは置いておいて、親データを削除する方法が用意されています。論理的に言えば、親データが無くなった時点で子データにもデータがなくなれば良いわけです。その方法論としては二つあって、一つは該当する子データを削除します。もう一つは、該当する子データに NULL を設定するというものです。これを実現するには、制約そのものを作成しなおす必要があります。

ALTER TABLE 店頭商品マスタ
DROP
	constraint 制約名_店頭商品マスタ参照;

ALTER TABLE 店頭商品マスタ
ADD
	constraint 制約名_店頭商品マスタ参照
	foreign key (商品分類)
	references 商品分類マスタ(コード)
	on delete CASCADE;

CASCADE の代わりに SET NULL が指定できます

親表を削除

どうしても、親表を削除したいばあいは普通に考えれば制約を削除してしまえば可能になります。しかし、そういう場合を想定して、drop table 構文にオプションが用意されています。これによって同時に制約も削除されます

drop TABLE 商品分類マスタ
	cascade constraints

表内指定

制約は、CREATE TABLE 文で列の定義の一部として記述する事ができます。この場合、構文上外部キーである事は明白なので foreign key キーワードは必要ありません

CREATE TABLE 店頭商品マスタ (
コード NUMBER(4,0)
,商品名 VARCHAR2(50)
,単価 NUMBER(7,0)
,商品分類 NUMBER(2,0)
   constraint 制約名_店頭商品マスタ参照 references 商品分類マスタ(コード)
,PRIMARY KEY (コード)
)