仅需简单的理清思路便可从USER_CONSTRAINTS和USER_CONS_COLUMNS视图中获得创建外键的必要信息，进而可以按需生成自己需要微调的外键创建脚本。
例如想批量生成带有“on delete cascade”属性的外键调整语句时，SQL创建的方法是一个比较灵活手段。

1.直接了当，脚本如下。
select    'alter table '
       || a.table_name
       || ' add constraint '
       || a.constraint_name
       || ' foreign key ('
       || c.column_name
       || ') references '
       || b.table_name
       || ' ('
       || b.column_name
       || ');'
          as "Foreing Key SQL"
from USER_CONSTRAINTS A, USER_CONS_COLUMNS B, USER_CONS_COLUMNS C
where A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
   and A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
   and A.TABLE_NAME = C.TABLE_NAME
   and B.POSITION = C.POSITION
   and A.TABLE_NAME = '&Table_Name'
/

2.演示一下脚本使用效果
沿用《【Analytic】使用分析函数ROW_NUMBER辅助完成外键的索引批量创建》http://space.itpub.net/519536/viewspace-625326中的主外键表的例子演示一下脚本的效果。
sec@ora10g> select    'alter table '
2         || a.table_name
3         || ' add constraint '
4         || a.constraint_name
5         || ' foreign key ('
6         || c.column_name
7         || ') references '
8         || b.table_name
9         || ' ('
10         || b.column_name
11         || ');'
12            as "Foreing Key SQL"
13    from USER_CONSTRAINTS A, USER_CONS_COLUMNS B, USER_CONS_COLUMNS C
14   where A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
15     and A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
16     and A.TABLE_NAME = C.TABLE_NAME
17     and B.POSITION = C.POSITION
18     and A.TABLE_NAME = '&Table_Name'
19 /
Enter value for table_name: T_CHILD
old 18:    and A.TABLE_NAME = '&Table_Name'
new 18:    and A.TABLE_NAME = 'T_CHILD'

Foreing Key SQL
-----------------------------------------------------------------------------------------------------------
alter table T_CHILD add constraint FK_T_CHILD_01 foreign key (COL1_CHILD) references T_PARENT (COL_PARENT);
alter table T_CHILD add constraint FK_T_CHILD_02 foreign key (COL2_CHILD) references T_PARENT (COL_PARENT);

3.对比一下使用“dbms_metadata.get_ddl”方法获得外键约束的结果
sec@ora10g> select dbms_metadata.get_ddl('TABLE','&Table_Name') as "Create table DDL" from dual;
Enter value for table_name: T_CHILD
old   1: select dbms_metadata.get_ddl('TABLE','&Table_Name') as "Create table DDL" from dual
new   1: select dbms_metadata.get_ddl('TABLE','T_CHILD') as "Create table DDL" from dual

Create table DDL
-------------------------------------------------------------------------

CREATE TABLE "SEC"."T_CHILD"
   (    "COL1_CHILD" NUMBER(*,0),
        "COL2_CHILD" NUMBER(*,0),
         CONSTRAINT "FK_T_CHILD_01" FOREIGN KEY ("COL1_CHILD")
          REFERENCES "SEC"."T_PARENT" ("COL_PARENT") ENABLE,
         CONSTRAINT "FK_T_CHILD_02" FOREIGN KEY ("COL2_CHILD")
          REFERENCES "SEC"."T_PARENT" ("COL_PARENT") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_D"

外键约束内容与我们组织的SQL内容一致。当然可以进一步对脚本进行调整，生成带有“on delete cascade”属性的外键创建语句（这个小任务留给大家当家庭作业啦）。

4.小结
在遇到具体需求的时候，不要盲目的钻牛角尖，首先想一想有没有便捷的手段来完成。

Good luck.

secooler
10.01.18

-- The End --

ITPub博客

【Tips】使用SQL生成外键的SQL创建语句

最新文章