1z0-071 Oracle Database 12c SQL 第22题 ON DELETE CASCADE语句

Q22. Examine the command:
SQL> ALTER TABLE books_transactions
ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)
REFERENCES books (book_id) ON DELETE CASCADE;
What does ON DELETE CASCADE imply?
A. When the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
B. When the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.
C. When a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Correct Answer: C

测试

SQL> create table A(  
  2  A_ID NUMBER,
  3  A_NAME VARCHAR2(20),
  4  A_AGE DATE);

表已创建。

SQL> 
SQL> CREATE TABLE B(
  2  B_ID NUMBER,
  3  A_ID NUMBER,
  4  B_NAME VARCHAR2(20),
  5  B_AGE DATE);

表已创建。

SQL> ALTER TABLE A ADD CONSTRAINT PPP PRIMARY KEY(A_ID);

表已更改。

SQL> 
SQL> ALTER TABLE B ADD CONSTRAINT QQQ PRIMARY KEY(B_ID);

表已更改。

SQL> ALTER TABLE B
  2  ADD CONSTRAINT fk_B FOREIGN KEY(A_ID)
  3  REFERENCES A(A_ID) ON DELETE CASCADE;

表已更改。

SQL> INSERT INTO A VALUES(101, 'A101', TO_DATE('2018/06/05', 'YYYY/MM/DD'));

已创建 1 行。

SQL> INSERT INTO A VALUES(102, 'A102', TO_DATE('2018/06/06', 'YYYY/MM/DD'));

已创建 1 行。

SQL> INSERT INTO A VALUES(103, 'A103', TO_DATE('2018/06/07', 'YYYY/MM/DD'));

已创建 1 行。

SQL> INSERT INTO B VALUES(1001, 101, 'B101', TO_DATE('2018/06/06', 'YYYY/MM/DD'));

已创建 1 行。

SQL> INSERT INTO B VALUES(1002, 102, 'B102', TO_DATE('2018/06/07', 'YYYY/MM/DD'));

已创建 1 行。

SQL> SELECT * FROM A;

      A_ID A_NAME               A_AGE
---------- -------------------- --------------
       101 A101                 05-6月 -18
       102 A102                 06-6月 -18
       103 A103                 07-6月 -18

SQL> SELECT * FROM B;

      B_ID       A_ID B_NAME               B_AGE
---------- ---------- -------------------- --------------
      1001        101 B101                 06-6月 -18
      1002        102 B102                 07-6月 -18

A、B选项
SQL> DROP TABLE A;
DROP TABLE A
           *
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用

再插入一条数据到B表
SQL> INSERT INTO B VALUES(1003, 103, 'B103', TO_DATE('2018/06/07', 'YYYY/MM/DD'));

已创建 1 行。

C选项
SQL> DELETE A WHERE A_ID=102;

已删除 1 行。

SQL> SELECT * FROM A;

      A_ID A_NAME               A_AGE
---------- -------------------- --------------
       101 A101                 05-6月 -18
       103 A103                 07-6月 -18

SQL>  SELECT * FROM B;

      B_ID       A_ID B_NAME               B_AGE
---------- ---------- -------------------- --------------
      1001        101 B101                 06-6月 -18
      1003        103 B103                 07-6月 -18

D选项
SQL> DELETE B WHERE B_ID=1003;

已删除 1 行。

SQL> SELECT * FROM B;   

      B_ID       A_ID B_NAME               B_AGE
---------- ---------- -------------------- --------------
      1001        101 B101                 06-6月 -18
      1002        102 B102                 07-6月 -18

SQL>  SELECT * FROM A;

      A_ID A_NAME               A_AGE
---------- -------------------- --------------
       101 A101                 05-6月 -18
       103 A103                 07-6月 -18
       102 A102                 06-6月 -18