Oracle 12C TRUNCATE TABLE CASCADE

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就可以極聯刪除。