Dropping a table in Oracle SQL

Question

Whenever I try to drop a table or create a table it is showing these errors:

DROP TABLE SUBURB;
DROP TABLE STOCKITEM;
DROP TABLE MANUFACTURER;
DROP TABLE WAREHOUSE;
DROP TABLE CITY;
DROP TABLE STATE;

Error at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys

 CREATE TABLE STATE (
 statecode varchar(3)
 ,statename varchar(30)
 ,population number(8)
 ,primary key(statecode)
  );

Error at line 1: ORA-00955: name is already used by an existing object

Can anybody explain why this happens?

Answer 1

If you're really sure you want to drop the table even though it's referenced in foreign keys you can force it like this:

drop table state cascade constraints;

This syntax is defined in the Oracle SQL Reference.

Note that this drops any foreign key relationships. So you will need to recreate them after you have rebuilt the table (and its primary key). Normally this is okay because the most common use case is trashing and re-creating schemas in Development or CI environments.

We can use cascade constraints to make our build scripts easier to maintain. There are two alternatives:

1. Explicitly drop the foreign key constraints before dropping the tables, either with a script or with dynamic SQL.
2. Order the DROP TABLE statements so that dependent tables are zapped first, along with their pesky foreign keys. Easy enough for a handful of tables, more painful with a large schema.

Answer 2

You can use below query to fetch the references of table which should be dropped before dropping the table.

select table_name, constraint_name, status, owner
from dba_constraints
where 1=1
--and r_owner = :p_owner --if you know schema
and constraint_type = 'R'
and r_constraint_name in
(
   select constraint_name from dba_constraints
   where constraint_type in ('P','U')
   and lower(table_name) = lower(:p_table_name)
   --and r_owner = :p_owner
)
order by table_name, constraint_name

Answer 3

if you create the primary key and also create the foreign key than you cannot drop the table you drop the table in this way for example if you have the table of students or teachers you want to drop this table you should write

DROP TABLE students CASCADE CONSTRAINTS; 

and also you drop the table of teachers

DROP TABLE teachers CASCADE CONSTRAINTS;