Created
March 14, 2011 19:33
-
-
Save sdeming/869717 to your computer and use it in GitHub Desktop.
Get DDL to create foreign key constraints in Oracle... Wooooo hoo!
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.