Oracle – drop foreign key without name Oracle

alterconstraintsoracle

I want to ask a very basic question here.
We may/may not name a constraint while creating a table or after creating the table.
Suppose I chose not to name the foreign key constraint.

The table is having no records.

Can I delete the foreign key name without naming it.

I know how to get name of foreign key and then delete using it like

alter table my_table drop constraint fk_name;

but I want to delete/drop the foreign key constraint without mentioning its name.

Is there anyway to do it?

Best Answer

but i want to delete/drop the foreign key constraint without mentioning its name.

That's not possible. The dropping a foreign key constraint requires a name. However you can find out the system generated name:

select constraint_name
from user_constraints
where table_name = 'MY_TABLE'
  and constraint_type = 'R';

Will show you all foreign keys defined on the table MY_TABLE. Using that statement you can even generate the necessary DDL statement:

select 'alter table "'||table_name||'" drop constraint "'||constraint_name||'";'
from user_constraints
where table_name = 'MY_TABLE'
  and constraint_type = 'R';

Save the output of that select into a file and you have the statement(s) to drop all foreign keys from that table.

Related Topic