Oracle 12C TRUNCATE TABLE CASCADE
The truncate table cascade statement is provided in Oracle 12c to delete the association when the TABLE has a primary foreign key relationship and is cleared. The following is a demonstration.
1. Prepare the test table
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. Let's test it using 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. Use 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
As you can see, TRUNCATE Oracle directly gives an association error.
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
You can use CASCADE to delete a polar link.