親テーブルに存在しないデータが子テーブルに存在することができないようにすることでデータの整合性を維持する機能である。

教育環境では外部キーを設定するのは当然だが、データの整合性を維持するために子テーブルを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) ;

Oracle12.1 SQL言語リファレンス constraint