CODEWITHSUNDEEP
sqloracle-databaseforeign-keys
sri
sri

Reputation: 109

Dropping a table in Oracle SQL

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?

Upvotes: 10

Views: 43666

Answers (5)

Ume habiba
Ume habiba

Reputation: 11

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;

Upvotes: 1

APC
APC

Reputation: 146219

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.

Upvotes: 16

Mohammad Heydari
Mohammad Heydari

Reputation: 4290

here's the solution that works for me fine in Oracle sample database : 

DROP TABLE ['Your_Table_Name'] STATE CASCADE CONSTRAINTS;

Upvotes: 0

Piyushkumar Kachhadiya
Piyushkumar Kachhadiya

Reputation: 151

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

Upvotes: 5

Bikash choudhury
Bikash choudhury

Reputation: 19

SUBURB Table is a parent table for any other table First you drop the child table then you can drop the SUBURB table....

And a table named as STATE is already present in your database...so you cant create the table having the same name....once if you drop the STATE table you can create another....

Upvotes: 0

Related Questions