DROP TABLE cascade Constraints in Oracle

Source: https://www.cnblogs.com/xd502djj/archive/2010/11/16/1878392.html

When you drop a table, deleting the table action causes trigger or constraint to conflict, and the system will receive an error warning message without allowing execution. A very simple example, such as you have an employee base table, which may have employee number and employee name fields, and an employee Sales table with employee number and employee Sales two fields, employee Salary table employee Number field is a foreign key reference to employee base table employee number :

sql> drop table t;

Table dropped.

sql> drop table T1;

Table dropped.

Sql> CREATE TABLE t (ID number,name varchar2 (20));

Table created.

sql> CREATE TABLE t1 (ID number,sal number);

Table created.

Sql> ALTER TABLE t add constraint T_PK primary key (ID);

Table altered.

Sql> ALTER TABLE t1 ADD constraint T_FK foreign key (ID) references T (ID);

Table altered.

Sql> INSERT INTO t values (1, ' JACK ');

1 row created.

Sql> INSERT INTO t values (2, ' MARY ');

1 row created.

Sql> COMMIT;

Commit complete.

sql> INSERT INTO T1 values (1,1000);

1 row created.

sql> INSERT INTO T1 values (2,1500);

1 row created.

Sql> commit;

sql> INSERT into T1 values (3,200);
INSERT into T1 values (3,200)
*
ERROR at line 1:
Ora-02291:integrity constraint (SYS. T_FK) violated-parent key not found

(Against the constraint, the Staff basic information table there is no 3 this employee, how to sell the record. )

sql> drop table t;
drop table T
*
ERROR at line 1:
ora-02449:unique/primary keys in table referenced by foreign keys

(in violation of constraint, the Employee sales form T1 has a reference to table T, this reference relation does not allow you to drop table T)

sql> DROP TABLE t cascade constraints;

Table dropped.

Sql> select * from T1;

ID SAL
---------- ----------
1 1000
2 1500

Sql> Select Constraint_name,table_name from dba_constraints where owner = ' SYS ' and table_name = ' T1 '

No rows selected

Sql>

We can find that the DROP table cascade constraints can be used to delete the constraint of the associated table T to achieve the purpose of your drop table T, which originally belonged to T1 's foreign key Constraint has been removed, but the data stored in table T1 will not be deleted, meaning that the DROP table cascade constraints does not affect the row data stored in OBJEC.

DROP TABLE cascade Constraints in Oracle