4

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Raghav
  • 195
  • 1
  • 3
  • 6

1 Answers1

10

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.