1.测试表的准备
SQL> CREATE TABLE t1 (
? 2? id? ? ? ? ? NUMBER,
? 3? description? VARCHAR2(50),
? 4? ? CONSTRAINT t1_pk PRIMARY KEY (id)
)? 5? ;
Table created.
SQL> CREATE TABLE t2 (
? 2? id? ? ? ? ? ? NUMBER,
? 3? t1_id? ? ? ? ? NUMBER,
? 4? ? description? ? VARCHAR2(50),
? 5? ? CONSTRAINT t2_pk PRIMARY KEY (id),
? ? 6? CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
? 7? );
Table created.
SQL> CREATE TABLE t3 (
? 2? id? ? ? ? ? ? NUMBER,
? 3? ? t2_id? ? ? ? ? NUMBER,
? 4? ? description? ? VARCHAR2(50),
? 5? CONSTRAINT t3_pk PRIMARY KEY (id),
? 6? ? CONSTRAINT t3_t2_fk FOREIGN KEY (t2_id) REFERENCES t2 (id) ON DELETE CASCADE
? 7? );
Table created.
SQL> INSERT INTO t1 VALUES (1, 't1 ONE');
1 row created.
SQL> INSERT INTO t2 VALUES (1, 1, 't2 ONE');
1 row created.
SQL> INSERT INTO t2 VALUES (2, NULL, 't2 TWO');
1 row created.
SQL> INSERT INTO t3 VALUES (1, 1, 't3 ONE');
1 row created.
SQL> INSERT INTO t3 VALUES (2, NULL, 't3 TWO');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
? 2? ? ? ? (SELECT COUNT(*) FROM t2) AS t2_count,
? 3? ? ? ? (SELECT COUNT(*) FROM t3) AS t3_count
FR? 4? OM? dual;
? T1_COUNT? T2_COUNT? T3_COUNT
---------- ---------- ----------
? ? ? ? 1? ? ? ? ? 2? ? ? ? ? 2
?2.我们使用DELETE CASCADE测试一下
SQL> DELETE FROM t1 CASCADE;
?1 row deleted.
?SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
? ? 2? ? ? ? (SELECT COUNT(*) FROM t2) AS t2_count,
? ? 3? ? ? ? (SELECT COUNT(*) FROM t3) AS t3_count
? 4? FROM? dual;
? T1_COUNT? T2_COUNT? T3_COUNT
?---------- ---------- ----------
? ? ? ? ? 1? ? ? ? ? 2? ? ? ? ? 2
?SQL> ROLLBACK;
?Rollback complete.
?SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
? ? 2? ? ? ? (SELECT COUNT(*) FROM t2) AS t2_count,
? 3? ? ? ? (SELECT COUNT(*) FROM t3) AS t3_count
? 4? FROM? dual;
? T1_COUNT? T2_COUNT? T3_COUNT
?---------- ---------- ----------
? ? ? ? ? 1? ? ? ? ? 2? ? ? ? ? 2
?3.使用TRUNCATE CASCADE
?SQL> TRUNCATE TABLE t1;
?TRUNCATE TABLE t1
? ? ? ? ? ? ? ? *
?ERROR at line 1:
?ORA-02266: unique/primary keys in table referenced by enabled foreign keys
可以看到直接TRUNCATE Oracle会给出有关联关系的错误。
SQL> TRUNCATE TABLE t1 CASCADE;
?Table truncated.
?SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
? ? 2? ? ? ? (SELECT COUNT(*) FROM t2) AS t2_count,
? ? 3? ? ? ? (SELECT COUNT(*) FROM t3) AS t3_count
?FR? 4? OM? dual;
? T1_COUNT? T2_COUNT? T3_COUNT
?---------- ---------- ----------
? ? ? ? ? 0? ? ? ? ? 0? ? ? ? ? 0
使用CASCADE就可以极联删除。