2009年07月08日


参照整合性制約

関連性のある二つのテーブルに関して、
約束事を「制約」として定義し、整合性が保たれるようにします
( 矛盾しないようにする )

親表が先に必要なので、社員表から管理者が設定されていない
データを管理者データとして抽出します
create table 管理者 (
	社員コード
	,氏名
	,フリガナ
	,支給額 default 0
	,primary key( 社員コード )
)
 as
select
	社員コード
	,氏名
	,フリガナ
	,NVL(給与,0)+NVL(手当,0)
from
	社員マスタ
where
	管理者 is NULL


外部キーを定義する「一般社員」表を定義します。

references 管理者(社員コード) が外部キーの定義です
create table 一般社員 (
	社員コード varchar2(4)
	,管理者コード varchar2(4) references 管理者(社員コード)
	,氏名 varchar2(50)
	,フリガナ varchar2(50)
	,支給額 number default 0
	,primary key( 社員コード )
)

社員コードが主キーの場合は、references 管理者 として省略可能です
テーブル単位で指定する場合は、最後に
,constraint 制約名 foreign key(管理者コード) references 管理者(社員コード)
を指定します( この場合も社員コードは主キーなので省略できます )

「一般社員」表に、社員表からデータを投入します
insert into 一般社員
select
	社員コード
	,管理者
	,氏名
	,フリガナ
	,NVL(給与,0)+NVL(手当,0)
	from 社員マスタ 
	where
		管理者 is not NULL

単純な( 条件の無い ) 列指定の外部キーを定義しています。

相手(親表) の主キーか、ユニークキーを参照します

これによって、一般社員表と管理者表には関係が保たれるように
以下の処理が禁止されます
★ 親表に無い管理者を一般社員表で指定できない
★ 一般社員から参照されている親表のレコードは削除ではない
★ 親表は削除できない
★ 親表に truncate table を実行できない


※ 但し、一般社員表側で NULL を指定する事は許されています

削除可能にする為の外部キー制約作成時のオプション
Oracle ドキュメントの参照

制約定義の最後に 

on delete cascade 

または、

on delete set null

を追加する事によって、親表のデータ削除と同時に整合性を保つ
為の子表に対するアクションを定義できます
親表を強制的に削除する
制約が原因で削除できないので、制約を削除してから表を削除するか、

drop table の最後に cascade constraints を追加します


posted by at 2009-07-08 19:47 | 暗記 | このブログの読者になる | 更新情報をチェックする
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。