11.19.2 参照制約の定義
参照制約を有効にするためには,外部キーによって参照される主キーを被参照表に定義しておく必要があります。定義系SQLのCREATE TABLEで被参照表にPRIMARY KEY(主キー)を指定します。また,検査保留状態を使用するには,pd_check_pendingオペランドにUSEを指定するか,又はオペランドの指定を省略します。
参照表には,FOREIGN KEY(外部キー)を指定し,FOREIGN KEY句中に次の指定をします。
-
参照する列
-
被参照表
-
参照制約動作
参照制約動作は被参照表に対する挿入,更新,又は削除時の動作をCASCADE,又はRESTRICTで指定します。
CASCADE,又はRESTRICTを指定した場合の被参照表と参照表の動作について説明します。
- 〈この項の構成〉
(1) CASCADEを指定している場合
CASCADEを指定すると,被参照表の主キーに変更があった場合,外部キーも同じように変更されます。なお,参照表の外部キーに変更がある場合,主キーに変更後の値と同じ値の行があるかどうかをチェックして,参照制約違反エラーになれば外部キーは変更されません。
CASCADEを指定している場合,被参照表及び参照表にSQLを実行するときの動作の例を次の図に示します。
- 〔説明〕
-
主キーの値と同じ値の行が外部キーにあれば,制約を保持するために,外部キーも主キーと同じように変更されます。この場合,被参照表への更新は実行されます。挿入及び削除も同じです。
図11‒34 参照表に更新SQLを実行するときの動作の例(CASCADE指定時) - 〔説明〕
-
更新後の外部キーの値と同じ値の行が主キーにあれば,外部キーへの更新が実行されます。同じ値の行がなくても,外部キーにナル値があるときは外部キーへの更新が実行されます。ナル値がないときは参照制約違反エラーになります。この場合,被参照表には特に影響はありません。挿入及び削除も同じです。
CASCADEを指定している場合の主キーに対する操作と参照表の動作と外部キーに対する操作と被参照表の動作を次の表に示します。
主キーに対する操作 |
被参照表と参照表の行の関係 |
主キーに対する操作結果 |
参照表の動作 |
---|---|---|---|
挿入(INSERT文) |
なし |
○ |
動作しない |
更新(UPDATE文), 削除(DELETE文) |
更新前の主キー構成列の値と同じ外部キー構成列の値を持つ行が,参照表にある |
○ |
主キーと同じ値で更新,又は行削除 |
更新前の主キー構成列の値と同じ外部キー構成列の値を持つ行が,参照表にない |
○ |
動作しない |
- (凡例)
-
○:正常に実行されます。
表11‒13 外部キーに対する操作と被参照表の動作(CASCADE指定時) 外部キーに対する操作
参照表と被参照表の行の関係
外部キーに対する操作結果
被参照表の動作
挿入(INSERT文)
挿入する行の外部キー構成列の値と同じ主キー構成列の値を持つ行が被参照表にある
○
動作しない
挿入する行の外部キー構成列の値と同じ主キー構成列の値を持つ行が被参照表にない
外部キー構成列中にナル値がある
○
外部キー構成列中にナル値がない
×
更新(UPDATE文)
更新後の外部キー構成列の値と同じ主キー構成列の値を持つ行が被参照表にある
○
動作しない
更新後の外部キー構成列の値と同じ主キー構成列の値を持つ行が被参照表にない
外部キー構成列中にナル値がある
○
外部キー構成列中にナル値がない
×
削除(DELETE文)
なし
○
動作しない
- (凡例)
-
○:正常に実行されます。
×:制約違反エラーとなります。
なお,CASCADEを指定すると,主キーの変更を外部キーにも反映するため,表定義時にHiRDBが内部的にトリガを生成します。参照制約動作のトリガ,及びユーザが定義するトリガとの関係については,「参照制約とトリガ」を参照してください。
(2) RESTRICTを指定している場合
RESTRICTを指定すると,被参照表の主キーに変更がある場合,外部キーに同じ値の行があれば,参照制約違反エラーになり,主キーは変更されません。なお,外部キーに変更がある場合,主キーに同じ値の行があるかどうかをチェックして,参照制約違反エラーになれば外部キーは変更されません。
RESTRICTを指定している場合,被参照表に更新SQLを実行するときの動作を次の図に示します。参照表の動作は,CASCADE指定時(図「参照表に更新SQLを実行するときの動作の例(CASCADE指定時)」を参照)と同じです。
- 〔説明〕
-
主キーの値と同じ値の行が外部キーにあれば,参照制約違反エラーになり,主キーへの更新は実行されません。同じ値の行がなければ,被参照表への更新が実行されます。挿入及び削除も同じです。
RESTRICTを指定している場合の主キーに対する操作と被参照表及び参照表の動作を次の表に示します。外部キーに対する操作と被参照表の動作はCASCADE指定時(表「外部キーに対する操作と被参照表の動作(CASCADE指定時)」を参照)と同じです。
主キーに対する操作 |
被参照表と参照表の行の関係 |
主キーに対する操作結果 |
参照表の動作 |
---|---|---|---|
挿入(INSERT文) |
なし |
○ |
動作しない |
更新(UPDATE文), 削除(DELETE文) |
更新前の主キー構成列の値と同じ外部キー構成列の値を持つ行が,参照表にある |
× |
動作しない |
更新前の主キー構成列の値と同じ外部キー構成列の値を持つ行が,参照表にない |
○ |
- (凡例)
-
○:正常に実行されます。
×:制約違反エラーとなります。
(3) 被参照表,及び参照表定義時の制限事項
被参照表と参照表の表定義,表定義変更,及び表削除時の制限事項を次に示します。
(a) 表定義(CREATE TABLE)時
-
同じ外部キー構成列(並びが同じでなくてもよい)の外部キーから,同じ被参照表を参照することはできません。
-
次の場合,外部キーは定義できません。
-
WITHOUT ROLLBACKを指定した表,共用表,及び改竄防止表の場合
-
被参照表がWITHOUT ROLLBACKを指定した表の場合
-
共用表の場合
-
改竄防止表の場合
-
一時表の場合
-
被参照表が一時表の場合
-
-
外部キーは,一つの表に255個まで定義できます。256個以上は定義できません。
-
一つの主キーに対して,外部キーは255個まで定義できます。256個以上は定義できません。
-
参照表定義時に参照できる表は,同一スキーマの表だけです。
-
次の条件をすべて満たす場合にだけ,一つの表で,同じ主キーを参照するON UPDATE CASCADE(更新時の参照制約動作がCASCADE)を指定した参照表を定義できます。
-
複数の外部キー構成列が重複していない
-
複数の外部キー構成列に関連する検査制約,又は参照制約を定義していない
-
-
外部キーの文字集合と,その外部キーから参照される表の主キーの文字集合は同じにしてください。
(b) 表定義変更(ALTER TABLE時)
-
被参照表及び参照表に対して,DROP句及びRENAME句を使用した表定義変更はできません。
-
被参照表の主キー構成列,外部キー構成列に対して定義を変更する場合,次の制限があります。
-
CHANGE句を使用したデータ型やデータ長の変更はできない
-
RENAME句を使用した列名変更はできない
-
-
WITH PROGRAMを指定した場合,参照表が参照する被参照表を使用する関数,手続き,及びトリガのSQLオブジェクトは無効になります。そのため,ALTER ROUTINE,ALTER PROCEDURE,又はALTER TRIGGERで再作成する必要があります。
(c) 表削除(DROP TABLE)時
-
外部キーから参照される被参照表は削除できません。
(4) 参照制約を定義する場合の注意事項
-
次の条件をすべて満たす場合,被参照表と参照表間でデッドロックが発生することがあります。これらの条件は参照制約動作がRESTRICTでもCASCADEでも同じです。
-
参照表の行を更新するトランザクションと,被参照表を更新するトランザクションが異なるトランザクションで,かつ同時に実行される
-
参照表で更新する行の主キー構成列の値と,被参照表で更新する行の外部キー構成列の値が同じである
被参照表及び参照表を操作する場合,上記条件が重ならないようにしてください。なお,それぞれのトランザクションで,操作対象の表に対してLOCK文の排他モードで排他制御することでデータの整合性は保証できます。ただし,同時実行性は低下します。
-
-
SQLオブジェクト用バッファ長の容量見積もり
参照制約動作を指定すると,HiRDBが内部的に制約条件チェックや参照制約動作を実行するトリガを生成するため,SQLオブジェクト用バッファを指定するときにそれらのSQLオブジェクトについても考慮する必要があります。SQLオブジェクト用バッファ長(pd_sql_object_cache_size)の見積もり式については,マニュアル「HiRDB システム定義」を参照してください。
-
データディクショナリLOB用RDエリアの容量見積もり
参照制約動作にCASCADEを指定すると,参照制約動作を実行するトリガをHiRDBが生成します。このトリガのトリガ動作手続きのSQLオブジェクトはデータディクショナリLOB用RDエリアに格納されます。そのため,参照制約動作にCASCADEを指定する場合はデータディクショナリLOB用RDエリアに十分な容量を確保しておく必要があります。データディクショナリLOB用RDエリアの容量の見積もりについては,「データディクショナリLOB用RDエリアの容量の見積もり」を参照してください。
-
バックアップの取得
バックアップは,被参照表が格納されている全RDエリア,及び参照表が格納されている全RDエリアの同期を合わせて取得してください。インナレプリカ機能を使用している場合は全RDエリアの世代番号を合わせてバックアップを取得してください。
また,バックアップ取得時点の検査保留状態によって,バックアップの取得範囲が異なります。バックアップの取得時点と取得範囲については,マニュアル「HiRDB システム運用ガイド」の「同時にバックアップを取得する必要があるRDエリア」を参照してください。
(5) 参照制約の定義例
参照制約の定義例を次に示します。
(a) 1対1対応で参照制約を定義する例
被参照表と参照表が1対1の場合の定義例を次に示します。
- 参照制約の定義例1
CREATE TABLE SEIZOUMOTO (SNO CHAR(4),SNAME NCHAR(6),TELEPHONE CHAR(12)) PRIMARY KEY(SNO) …主キーの指定 CREATE TABLE SHOHIN (GNO CHAR(4),SNO CHAR(4),GNAME NCHAR(10),SURYO INTEGER) CONSTRAINT SHOHIN_FK …制約名の指定 FOREIGN KEY(SNO) …外部キーの指定 REFERENCES SEIZOUMOTO …被参照表名の指定
- 参照制約動作の内容
-
参照制約動作の指定を省略しているため,更新及び削除時はRESTRICTが仮定されます。製造元表の製造元番号(主キー)の更新,削除をする場合,商品表の製造元番号(外部キー)に対応する行があると,参照制約違反エラーとなり,製造元表の製造元番号の更新,削除は抑止されます。
- 参照制約の定義例2
CREATE TABLE SEIZOUMOTO (SNO CHAR(4),SNAME NCHAR(6),TELEPHONE CHAR(12)) PRIMARY KEY(SNO) …主キーの指定 CREATE TABLE SHOHIN (GNO CHAR(4),SNO CHAR(4),GNAME NCHAR(10),SURYO INTEGER) CONSTRAINT SHOHIN_FK …制約名の指定 FOREIGN KEY(SNO) …外部キーの指定 REFERENCES SEIZOUMOTO …被参照表名の指定 ON UPDATE CASCADE …更新時の参照制約動作の指定 ON DELETE CASCADE …削除時の参照制約動作の指定
- 参照制約動作の内容
-
製造元表の製造元番号(主キー)を更新した場合,対応する商品表の製造元番号(外部キー)も主キーと同じ値に更新されます。製造元表の行を削除した場合,商品表に対応する行も削除されます。
(b) 1対2対応で参照制約を定義する例
被参照表が1,参照表が2の場合の定義例を次に示します。
- 参照制約の定義例
CREATE TABLE SHOHIN (GNO CHAR(4),SNO CHAR(4),GNAME NCHAR(10),SURYO INTEGER) PRIMARY KEY(GNO) …主キーの指定 CREATE TABLE SIIRE (GNO CHAR(4),GNAME NCHAR(10),SSURYO INTEGER) CONSTRAINT SIIRE_FK …制約名の指定 FOREIGN KEY(GNO) …外部キーの指定 REFERENCES SHOHIN …被参照表名の指定 ON UPDATE CASCADE …更新時の参照制約動作の指定 ON DELETE CASCADE …削除時の参照制約動作の指定 CREATE TABLE URIAGE (DNO CHAR(4),CNO CHAR(4),GNO CHAR(4),USURYO INTEGER) CONSTRAINT URIAGE_FK …制約名の指定 FOREIGN KEY(GNO) …外部キーの指定 REFERENCES SHOHIN …被参照表名の指定 ON UPDATE RESTRICT …更新時の参照制約動作の指定 ON DELETE RESTRICT …削除時の参照制約動作の指定
- 参照制約動作の内容
-
商品表の商品番号(主キー)を更新する場合,売り上げ表の商品番号(外部キー)に更新前の主キーと同じ値の行があると,参照制約違反エラーとなり,更新は抑止されます。売り上げ表に更新前の主キーと同じ値の行がないときは仕入れ表の対応する商品番号も主キーと同じ値に更新されます。
商品表の行を削除する場合,売り上げ表に更新前の主キーと同じ値の行があると,参照制約違反エラーとなり,削除は抑止されます。売り上げ表で更新前の主キーと同じ値の行がないときは仕入れ表の対応する行も削除されます。
(c) 2対1対応で参照制約を定義する例
被参照表が2,参照表が1の場合の定義例を次に示します。
- 参照制約の定義例
CREATE TABLE SHOHIN (GNO CHAR(4),SNO CHAR(4),GNAME NCHAR(10),SURYO INTEGER) PRIMARY KEY(GNO) …主キーの指定 CREATE TABLE KOKYAKU (CNO CHAR(4),CNAME NCHAR(8),ADDR NCHAR(24)) PRIMARY KEY(CNO) …主キーの指定 CREATE TABLE URIAGE (DNO CHAR(4),CNO CHAR(4),GNO CHAR(4),USURYO INTEGER) CONSTRAINT URIAGE_SHOHIN_FK …制約名の指定 FOREIGN KEY(GNO) …外部キーの指定 REFERENCES SHOHIN …被参照表名の指定 ON UPDATE CASCADE …更新時の参照制約動作の指定 ON DELETE CASCADE …削除時の参照制約動作の指定 CONSTRAINT URIAGE_KOKYAKU_FK FOREIGN KEY(CNO) …外部キーの指定 REFERENCES KOKYAKU …被参照表名の指定 ON UPDATE CASCADE …更新時の参照制約動作の指定 ON DELETE CASCADE …削除時の参照制約動作の指定
- 参照制約動作の内容
-
商品表の商品番号(主キー)を更新する場合,売り上げ表の商品番号(外部キー)も同じ値に更新されます。商品表の行を削除する場合,売り上げ表の対応する行も削除されます。
顧客表の顧客番号(主キー)を更新する場合,売り上げ表の顧客番号(外部キー)も同じ値に更新されます。顧客表の行を削除する場合,売り上げ表の対応する行も削除されます。