Q:

Drop foreign-key constraint

How to drop a foreign key if I have not named it during creation
create table abc(
id number(10),
foreign key (id) references tab(roll)
);

even
 alter table abc drop foreign key mn_ibfk_1;

is not working for me. I am using Oracle 10g.

A:

As you did not specify a constraint name, Oracle generated one for you (something like SYS_034849548).
You need to find the constraint name in order to be able to drop it:
select constraint_name
from user_constraints
where table_name = 'ABC'
  and constraint_type = 'R'

will display the constraint name. Then you can drop the constraint using:
alter table abc drop constraint <constraint_name>;

(replace <constraint_name> with the name you retrieved using the SQL statement)
Note that the syntax is alter table ... drop constraint. There is no drop foreign key.

