SQL: Trouble dropping foreign key constraint?

Question

Hi,

I am trying to remove a foreign key constraint from one of my tables in SQL Plus, and I keep getting errors. I have seen online that the syntax for dropping a foreign key constraint from a table is

ALTER TABLE table_name drop CONSTRAINT constraint_name;

But how do I know the "name" of the constraint? Specifically, I have a table created with the following command:

create table depositor
(customer_name varchar(15) not null,
account_number varchar(15) not null,
primary key(customer_name, account_number),
foreign key(account_number) references account(account_number));

I have tried to remove the constraint this way:

alter table depositor
drop foreign key account_number;

and it says, "error, missing keyword." Is "account_number" not the correct name for this constraint? Or am I doing something else wrong?

Thanks!

Thanks.

Answer 1

Since you didn't specify a name for the foreign key constraint, Oracle assigned one for you.



To find that name, try this query:



select constraint_name

from all_constraints

where table_name = 'DEPOSITOR' AND constraint_type = 'R';



(Note depositor must be UPPER CASE here).



That should return 1 row. The constraint name will probably be something like SYS_C000###



Assuming it returns 1 row, then you can do the ALTER TABLE table_name drop CONSTRAINT on that constraint name.

Answer 2

Drop Foreign Key

Answer 3

I don't even think you have a constraint.



Your create table statement is incorrect. I don't even know how that worked as is.



it should be Constraint pk_depositor Primary key(......



You're missing the keyword Constraint.