Does a drop table also drop the constraints?

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

SQL> desc t69 Name               Null?    Type ------------------ -------- ---------------------------- ID                 NOT NULL NUMBERSQL> select index_name from user_indexes  where table_name = 'T69';INDEX_NAME------------------------------SYS_C0034158SQL> select constraint_name, constraint_type from user_constraints where table_name = 'T69';CONSTRAINT_NAME                C------------------------------ -SYS_C0034157                   CSYS_C0034158                   PSQL> select trigger_name from user_triggers where table_name = 'T69';TRIGGER_NAME------------------------------TRG69SQL> 

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 selectedSQL> select trigger_name from user_triggers where table_name = 'T69';no rows selectedSQL> SQL> select index_name from user_indexes  where table_name = 'T69';no rows selectedSQL> 

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 keysSQL> 

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                                                 NUMBERSQL> select * from v23  2  /select * from v23              *ERROR at line 1:ORA-04063: view "FOX.V23" has errorsSQL> 

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.