Skip to content

Instantly share code, notes, and snippets.

@sdeming
Created March 14, 2011 19:33
Show Gist options
  • Save sdeming/869717 to your computer and use it in GitHub Desktop.
Save sdeming/869717 to your computer and use it in GitHub Desktop.
Get DDL to create foreign key constraints in Oracle... Wooooo hoo!
select 'alter table ' || source_table || ' add constraint ' || constraint_name || ' foreign key (' || con_columns || ') references ' || target_table || ' (' || ind_columns || ') enable' data
from (select constraint_name, source_table, target_index, target_table, con_columns, wm_concat(column_name) ind_columns
from (select a.constraint_name, a.source_table, a.target_index, b.table_name target_table, a.con_columns, b.column_name, b.column_position
from (select a.constraint_name, a.source_table, a.target_index, wm_concat(a.column_name) con_columns
from (select a.constraint_name,
a.table_name source_table,
a.r_constraint_name target_index,
b.column_name,
b.position
from user_constraints a
inner join user_cons_columns b on (b.constraint_name = a.constraint_name)
where a.constraint_type = 'R'
order by a.constraint_name, b.position) a
group by constraint_name, source_table, target_index) a
inner join user_ind_columns b on (b.index_name = a.target_index)
order by constraint_name, b.column_position)
group by constraint_name, source_table, target_index, target_table, con_columns);
@ashokkumaran
Copy link

Looks like "wm_concat" used in above sql is a function/procedure is missing. Please post that function/procedure here to execute above sql.

Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment