親テーブルに存在しないデータが子テーブルに存在することができないようにすることでデータの整合性を維持する機能である。
教育環境では外部キーを設定するのは当然だが、データの整合性を維持するために子テーブルをINSERT, UPDATEする度に親テーブルのデータの存在チェックするため負荷が少なくない。DWHなどデータ量がとても多い本番環境ではパフォーマンスを確保するため、あえて外部キーを設定しないことが多い。そのかわり、時々親テーブルに存在しないデータが子テーブルに存在しないかチェックする必要がある。
SQL
-- 準備
CREATE TABLE PARENTS -- 親
(
PARENT_ID NUMBER(11) NOT NULL -- 親ID
, PARENT_NAME VARCHAR2(256) -- 親の名前
, CONSTRAINT PARENTS_PK PRIMARY KEY(PARENT_ID) USING INDEX -- PRIMARY KEY
) ;
CREATE TABLE CHILDREN -- 子
(
PARENT_ID NUMBER(11) NOT NULL -- 親ID
, CHILD_ID NUMBER(2) NOT NULL -- 子ID
, CHILD_NAME VARCHAR2(256) -- 子の名前
, CONSTRAINT CHILDREN_PK PRIMARY KEY(PARENT_ID, CHILD_ID) USING INDEX -- PRIMARY KEY
, CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK -- 外部キー どのテーブルとどのテーブルのどの列の外部キーか想像できる制約名にする
FOREIGN KEY(PARENT_ID) REFERENCES PARENTS(PARENT_ID) ;
SQL
-- データを用意(パフォーマンスの調査には実際のデータに見合うデータ量を確保する)
INSERT INTO PARENTS(PARENT_ID, PARENT_NAME) VALUES(1, 'Padmé Amidala Naberrie') ;
INSERT INTO PARENTS(PARENT_ID, PARENT_NAME) VALUES(2, 'Anakin Skywalker') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(1, 1, 'Luke Skywalker') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(1, 2, 'Leia Organa') ;
COMMIT ;
SQL
-- INSERT すると外部キー エラーになるデータを追加
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(5, 1, '(Han Solo') ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(5, 1, '(Han Solo')
*
行1でエラーが発生しました。:
ORA-02291: 整合性制約(USERNAME1.CHILDREN_PARENTS_PARENT_ID_FK)に違反しました -
親キーがありません
-- と親キーが見つからないからエラーになる。
SQL
-- 外部キーをDROP
ALTER TABLE CHILDREN DROP CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK ;
表が変更されました。
SQL
-- INSERT すると外部キー エラーになるデータを追加
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID, CHILD_NAME) VALUES(5, 1, '(Han Solo') ;
1行が作成されました。
COMMIT ;
コミットが完了しました。
SQL
-- 改めて外部キーを追加
ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK
FOREIGN KEY(PARENT_ID) REFERENCES PARENTS(PARENT_ID) ;
ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK
*
行1でエラーが発生しました。:
ORA-02298: (USERNAME1.CHILDREN_PARENTS_PARENT_ID_FK)を検証できません -
親キーが見つかりません。
-- と無事外部キーエラーになる。
SQL
-- 今回は Han Solo が外部キーエラーになることがわかっているが、
-- 今まで外部キーがなかった場合は副問合せで親キーが見つからないデータを探す。
SELECT
CS.PARENT_ID
, CS.CHILD_ID
FROM CHILDREN CS
WHERE NOT EXISTS
(
SELECT
PS.PARENT_ID
FROM PARENTS PS
WHERE PS.PARENT_ID = CS.PARENT_ID
)
ORDER BY
CS.PARENT_ID
, CS.CHILD_ID ;
PARENT_ID CHILD_ID
------------- -------------
5 1
1行が選択されました。
SQL
-- で親キーが見つからないデータを特定できるので、親データを追加するか、間違った小データをDELETEする。
DELETE FROM CHILDREN CS
WHERE
CS.PARENT_ID = 5
AND CS.CHILD_ID = 1 ;
1行が削除されました。
COMMIT ;
SQL
-- これで外部キーを追加できる
ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK
FOREIGN KEY(PARENT_ID) REFERENCES PARENTS(PARENT_ID) ;
表が変更されました。
SQL
-- 外部キーで参照されていテーブルはTRUNCATE TABLEできない。
-- たとえ、外部参照しているテーブルのレコード件数がゼロであろうと。
TRUNCATE TABLE PARENTS ;
TRUNCATE TABLE PARENTS
*
行1でエラーが発生しました。:
ORA-02266:
表には有効な外部キーによって参照される一意キー/主キーが含まれています。
SQL
-- いったん外部キーをDROP
ALTER TABLE CHILDREN DROP CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK ;
表が変更されました。
SQL
-- 改めて TRUNCATE TABLE
TRUNCATE TABLE PARENTS ;
表が切り捨てられました。
SQL
-- このテーブルを外部参照しているテーブルにデータを追加し、データの整合性が確保できたら
-- 外部キーを追加できる
ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_PARENTS_PARENT_ID_FK
FOREIGN KEY(PARENT_ID) REFERENCES PARENTS(PARENT_ID) ;