Remove unnamed foreign key Oracle
I want to ask a very simple question here. We can / cannot name the constraint when creating a table or after creating a table. Let's say I choose not to specify a foreign key constraint.
There are no entries in the table.
Can I remove the foreign key name without naming it.
I know how to get the foreign key name and then remove it using
alter table my_table drop constraint fk_name;
but I want to drop / drop a foreign key constraint without mentioning its name.
Is there a way to do this?
source to share
but I want to drop / drop a foreign key constraint without mentioning its name.
It's impossible. A name is required to drop a foreign key constraint. 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 in the table MY_TABLE
. Using this statement, you can even create the required 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 this selection to a file and you have statement (s) to remove all foreign keys from this table.
source to share