● 2_04_35-外部参照性制約
上の図を参照して下さい。「e_hanbai_t」テーブルは販売履歴を検索するために「e_shouhin_t」の商品情報、「e_kokyaku_t」の顧客情報を見なければなりません。
販売履歴に登録されているデータで、もし参照している商品、顧客のデータが削除されていたらテーブルを結合してレコードを表示することができません。
この主キー、外部キーの関係を定義するのが外部参照性制約です。
外部参照性制約の構文です。
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名
|
制約名は任意の名称を付けます。
参照オプションは以下があります。
NO ACTION 制約がチェックされた時に参照行がまだ存在していた場合に、エラーとなることを意味しています
RESTRICT 被参照行が削除されるのを防ぎます。
CASCADE 被参照行が削除された時、それを参照する行（複数可）も同様に削除されなければならないことを指定します
SET NULL 被参照行が削除された際に、参照列がNULLに設定されるようになります。
SET DEFAULT 被参照行が削除された際に、参照列がデフォルトに設定されるようになります。
MATCH FULL 参照する外部キーの列にNULLを含ない場合は指定します
参照オプションを付けない場合はデフォルトとして参照行が削除されるのを防ぎます。
※ この制約を解除するコマンドは用意されていないのでテーブルの再作成が必要です。
文章で説明するよりSQLを発行してデータ削除を実行したほうが分かりやすいので例を出します。MATCH FULLを付けてNULLは認めません。
ALTER TABLE e_hanbai_t ADD CONSTRAINT e_hanbai_t_re FOREIGN KEY(e_kokyaku_id) REFERENCES e_kokyaku_t(e_kokyaku_id) MATCH FULL |
販売履歴に顧客マスタが含まれている時、その顧客マスタのデータが削除されるのを防ぎます。
設定が万一間違っているとデータを失うのでトランザクションをかけます。
先に販売履歴、顧客のデータを見てみましょう。下が顧客のデータです。
下が販売履歴です。
全ての顧客が販売履歴にあります、つまり一人も削除できないはずです。
まず「Ctrl」＋「b」でトランザクションを宣言します。顧客コード「000000000001」を削除します。
delete from e_kokyaku_t where e_kokyaku_id = '000000000001' |
以下のエラーメッセージがコンソールに出たはずです。
delete from e_kokyaku_t where e_kokyaku_id = '000000000001' SQLを実行中です... SQL実行中に以下のエラーが発生しました。 |
処理は行わなかったので「Ctrl」＋「k」でコミットしても大丈夫です。
顧客マスタを全検索して下さい。ちゃんと削除されずに「'000000000001」のデータがあります。
全ての顧客が履歴を持っていますから顧客データを全て削除できないはずです。下記を先のトランザクションの要領で実行して下さい。
delete from e_kokyaku_t |
下記のエラーが出たはずです、コミットしても大丈夫です。
SQL> SQLを実行中です... SQL実行中に以下のエラーが発生しました。 |
顧客マスタを全検索して下さい。全部あるはずです。
外部参照性制約の場合は参照しているレコードの削除を防ぐ事が多いのが通例ですが下記の場合も該当しないか設計段階で吟味して下さい。
CASCADE 被参照行が削除された時、それを参照する行（複数可）も同様に削除されなければならないことを指定します
SET NULL 被参照行が削除された際に、参照列がNULLに設定されるようになります。
SET DEFAULT 被参照行が削除された際に、参照列がデフォルトに設定されるようになります。
戻る