Q22. Examine the command:
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 tableare 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_IDmatches 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 valueis updated in the books transactons. BOOK_ID column.
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: 表中的唯一/主键被外键引用
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