ITPub博客

首页 > 数据库 > Oracle > Oracle 12c TRUNCATE TABLE CASCADE

Oracle 12c TRUNCATE TABLE CASCADE

原创 Oracle 作者:lucyne 时间:2015-11-09 11:11:35 0 删除 编辑
在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就可以极联删除。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26390465/viewspace-1825945/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
. . 一个 DBA. . .

注册时间:2012-01-06

  • 博文量
    84
  • 访问量
    742571