Does a drop table also drop the constraints?

Question

When I drop a table does it also drop the constraints?

Answer 1

Here is a simple table. It has an index, some integrity constraints and a trigger:

SQL> desc t69
 Name               Null?    Type
 ------------------ -------- ----------------------------
 ID                 NOT NULL NUMBER

SQL> select index_name from user_indexes  where table_name = 'T69';

INDEX_NAME
------------------------------
SYS_C0034158

SQL> select constraint_name, constraint_type from user_constraints where table_name = 'T69';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C0034157                   C
SYS_C0034158                   P

SQL> select trigger_name from user_triggers where table_name = 'T69';

TRIGGER_NAME
------------------------------
TRG69

SQL> 

Can we drop it? Yes we can!

SQL> drop table t69;

Table dropped.

SQL> select constraint_name, constraint_type from user_constraints where table_name = 'T69';

no rows selected

SQL> select trigger_name from user_triggers where table_name = 'T69';

no rows selected

SQL> 
SQL> select index_name from user_indexes  where table_name = 'T69';

no rows selected

SQL> 

Nothing remains. It's different when other objects reference the table.

There is another table, P23. It is referenced by a foreign key and used in a view.

SQL> create table c23 (id number, p_id number);

Table created.

SQL> alter table c23 add foreign key (p_id) references p23;

Table altered.

SQL> create view v23 as select * from p23;

View created.

SQL> 

So can we drop this table?

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


SQL> 

No we cannot. Incidentally regarding the RESTRICT syntax, that is not supported by Oracle. There's no need for it, we cannot drop tables which enforce relational integrity ... unless we insist upon doing so:

SQL> drop table p23 cascade constraints;

Table dropped.

SQL> desc t23
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLA                                               NUMBER
 COLB                                               NUMBER
 COLC                                               NUMBER
 GENDER                                             VARCHAR2(1)
 ID                                                 NUMBER

SQL> select * from v23
  2  /
select * from v23
              *
ERROR at line 1:
ORA-04063: view "FOX.V23" has errors


SQL> 

The CASCADE CONSTRAINTS clause drops the table and any foreign keys referencing it. The child tables remain otherwise intact. Views (and also any PL/SQL) referencing the table are left but in an invalid state.