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?
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.