参照整合性制約

  整合性制約のタイプ



制約の中では、もっとも面倒なものではありますが 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 キーワードの後に来る列名リストとして示されます。つまりこれらの用語は定義構文と1対1に対応しています。構文は結果的に以下のようになります

  
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 (コード)
)
  










  infoboard   管理者用   
このエントリーをはてなブックマークに追加




SQLの窓  天気  IT用語辞典
Yahoo!ニュース  マルチ辞書
PHP マニュアル  Google URL短縮 


フリーフォントWEBサービス
SQLの窓WEBサービス

SQLの窓フリーソフト

素材

一般WEBツールリンク

SQLの窓

フリーソフト

JSライブラリ