Drop foreign-key constraint

Question

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.

Answer 1

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.

Answer 2

Try this

alter table mn drop constraint mn_ibfk_1;

to find out for sure the name of the constraint try this query

SELECT a.table_name child_table,
        b.table_name parent_table,
        a.constraint_name child_constraint,
        b.constraint_name parent_constraint,
        c.column_name child_column,
        d.column_name parent_column
  FROM user_constraints a,
        user_constraints b,
        user_cons_columns c,
        user_cons_columns d
 WHERE        a.constraint_type = 'R'
        AND b.constraint_type = 'P'
        AND b.constraint_name = a.r_constraint_name
        AND A.table_name = c.table_name
        AND b.table_name = d.table_name
        AND a.constraint_name = c.constraint_name
        AND b.constraint_name = d.constraint_name
        AND c.position = d.position
        AND a.table_name = 'mn' ;