Drop table cascade constraints in ORACLE

When you try to drop a table, if the table deletion operation triggers a trigger or constraint conflict, the device shows an error warning message and does not allow execution. For example, an employee basic information table, which might have fields such as employee number and employee name, and another employee sales table with two fields, employee number and employee sales, and the employee compensation table, is a very simple example. The number field is an employee number referenced to the employee’s basic information table by a foreign key:

SQL> drop table test;

Table dropped.

SQL> drop table test1;

Table dropped.

SQL> create table test (id number,name varchar2(20));

Table created.

SQL> create table test1 (id number,sal number);

Table created.

SQL> alter table test add constraint t_pk primary key (id);

Table altered.

SQL> alter table test1 add constraint t_fk foreign key (id) references test (id);

Table altered.

SQL> insert into test values ​​(1,’JACK’);

1 row created.

SQL> insert into test values ​​(2,’MARY’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> insert into test1 values ​​(1,1000);

1 row created.

SQL> insert into test1 values ​​(2,1500);

1 row created.

SQL> commit;

SQL> insert into test1 values ​​(3,200);
insert into test1 values ​​(3,200)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.T_FK) violated-parent key not found

(Constraints are violated. The employee’s basic data sheet does not have any sales records for the employee number 3.

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

(Constraints are violated, employee sales table test1 has a reference to table test, this reference relation does not allow you to drop table test)

SQL> drop table test cascade constraints;

Table dropped.

SQL> select * from test1;

ID SAL
— — — — — — — — — —
1 1000
2 1500

SQL> select CONSTRAINT_NAME, TABLE_NAME from dba_constraints where owner =’SYS’ and TABLE_NAME =’test1'

no rows selected

SQL>

In order to achieve your drop table test, we can find that the use of Drop table cascade constraints can delete the limitation of the related table test. The international key restriction that originally belonged to test1 was removed, but the data stored in table test1 would not be deleted, meaning that the cascade constraints of the Drop table do not affect the row data stored in the object.

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.