转载

Oracle 12c TRUNCATE TABLE CASCADE

在Oracle 12c中提供了 TRUNCATE TABLE CASCADE语句，是为了表在有主外键关系时，清除主表时，关联删除。下面我们演示一下。
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就可以极联删除。

正文到此结束