FOREIGN KEY 外部キー ON DELETE CASCADE の有無での動作確認

FOREIGN KEY 外部キー(外部参照キー) はDELETE の動作を記述していないので、このページではその部分を記載する。

SQL

-- 準備
CREATE TABLE PARENTS  -- 親
    (
    PARENT_ID   NUMBER(1)  NOT NULL  -- 親ID
  , CONSTRAINT PARENTS_PK PRIMARY KEY(PARENT_ID) USING INDEX  -- PRIMARY KEY
    ) ;

CREATE TABLE CHILDREN  -- 子
    (
    PARENT_ID  NUMBER(1) NOT NULL  -- 親ID
  , CHILD_ID   NUMBER(1) NOT NULL  -- 子ID
  , CONSTRAINT CHILDREN_PK PRIMARY KEY(PARENT_ID, CHILD_ID) USING INDEX  -- PRIMARY KEY
  , CONSTRAINT CHILDREN_PARENTS_FK
        FOREIGN KEY(PARENT_ID) REFERENCES PARENTS(PARENT_ID)
        ON DELETE CASCADE  -- 親テーブルをDELETEすれば、それに紐付く子テーブルもいっしょにDELETEされる
    ) ;

SQL

-- データを用意
INSERT INTO PARENTS(PARENT_ID) VALUES(1) ;
INSERT INTO PARENTS(PARENT_ID) VALUES(2) ;
INSERT INTO PARENTS(PARENT_ID) VALUES(3) ;
INSERT INTO PARENTS(PARENT_ID) VALUES(4) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(1, 1) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(1, 2) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(2, 1) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(3, 1) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(3, 2) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(3, 3) ;
INSERT INTO CHILDREN(PARENT_ID, CHILD_ID) VALUES(4, 1) ;
COMMIT ;

SQL

SQL> -- PARENT_ID = 1 の親データを子データと共にDELETEする
SQL> DELETE FROM PARENTS PS WHERE PS.PARENT_ID = 1 ;

1行が削除されました。

SQL

SQL> -- DELETEされたことを確認
SQL> SELECT * FROM PARENTS ;

    PARENT_ID
-------------
            2
            3
            4

3行が選択されました。

SQL> SELECT * FROM CHILDREN ;

    PARENT_ID      CHILD_ID
------------- -------------
            2             1
            3             1
            3             2
            3             3
            4             1

5行が選択されました。

SQL

SQL> -- COMMIT を忘れずに
SQL> COMMIT ;

コミットが完了しました。

SQL

SQL> -- いったん外部キーをDROP
SQL> ALTER TABLE CHILDREN DROP CONSTRAINT CHILDREN_PARENTS_FK ;

表が変更されました。

SQL

SQL> -- ON DELETE CASCADE なしで外部キーをADD
SQL> ALTER TABLE CHILDREN ADD CONSTRAINT CHILDREN_PARENTS_FK
  2      FOREIGN KEY(PARENT_ID) REFERENCES PARENTS(PARENT_ID) ;

表が変更されました。

SQL

SQL> -- PARENT_ID = 2 の親データを子データと共にDELETEする
SQL> DELETE FROM PARENTS PS WHERE PS.PARENT_ID = 2 ;

DELETE FROM PARENTS PS WHERE PS.PARENT_ID = 2
*
行1でエラーが発生しました。:
ORA-02292: 整合性制約(USERNAME1.CHILDREN_PARENTS_FK)に違反しました -
子レコードがあります

-- と子データがあるから親データをDELETEできないと怒られる。子データを先にDELETEする。
SQL> DELETE FROM CHILDREN CS WHERE CS.PARENT_ID = 2 ;

1行が削除されました。

SQL

SQL> -- 改めてPARENT_ID = 2 の親データをDELETEする
SQL> DELETE FROM PARENTS PS WHERE PS.PARENT_ID = 2 ;

1行が削除されました。

SQL

SQL> -- COMMIT を忘れずに
SQL> COMMIT ;

コミットが完了しました。

SQL

SQL> -- 改めてDELETEされたことを確認
SQL> SELECT * FROM PARENTS ;

    PARENT_ID
-------------
            3
            4

2行が選択されました。

SQL> SELECT * FROM CHILDREN ;

    PARENT_ID      CHILD_ID
------------- -------------
            3             1
            3             2
            3             3
            4             1

4行が選択されました。

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

以上。