Saturday, November 16, 2013

Oracle 12c New Features: truncate table ... cascade


SQL> create table departments as select * from hr.departments;

Table created.

SQL> create table employees as select * from hr.employees;

Table created.

SQL> alter table departments add constraint departments_pk primary key (department_id);

Table altered.

SQL> alter table employees add constraint emp_dept_fk foreign key (department_id) references departments(department_id);

Table altered.

SQL> truncate table departments;
truncate table departments
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> truncate table departments cascade;
truncate table departments cascade
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table
"DONGHUA"."EMPLOYEES"

SQL> alter table employees drop constraint emp_dept_fk;

Table altered.

SQL> alter table employees add constraint emp_dept_fk foreign key (department_id) references departments(department_id) on delete cascade;

Table altered.

SQL> truncate table departments;
truncate table departments
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> truncate table departments cascade;

Table truncated.

SQL> select count(*) from departments;

  COUNT(*)
----------
         0

SQL>  select count(*) from employees;

  COUNT(*)
----------
         0

SQL> drop table departments purge;
drop table departments purge
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


SQL> drop table employees purge;

Table dropped.

SQL> drop table departments purge;

Table dropped.

SQL>