关于PowerDesigner15不能从PDM图生成MS SQL2005/2008外键约束的问题

最近用PowerDesigner15做数据库设计的时候发现如果先做好了CDM图，然后生成PDM，再生成SQL 2005或SQL 2008脚本，发现始终无法生成外键约束。

我估计很多人都遇到了类似的问题。    经过查找和摸索终于解决了这个问题，现在将过程叙述如下，希望对大家有帮助。

实验过程如下：

1、生成在CDM中建立两个实体Entity_1和Entity_2，并建立关系Relationship。

 

2、自动生成MS SQL Server 2008的PDM。

 

3、选择DBMS类型为SQL 2008然后生成Database脚本

/*==============================================================*/

/* DBMS name: Microsoft SQL Server 2008 */

/* Created on: 2010/4/5 19:36:09 */

/*==============================================================*/

 

 

if exists (select 1

from sysobjects

where id = object_id('Entity_1')

and type = 'U')

drop table Entity_1

go

 

if exists (select 1

from sysindexes

where id = object_id('Entity_2')

and name = 'Relationship_1_FK'

and indid > 0

and indid < 255)

drop index Entity_2.Relationship_1_FK

go

 

if exists (select 1

from sysobjects

where id = object_id('Entity_2')

and type = 'U')

drop table Entity_2

go

 

/*==============================================================*/

/* Table: Entity_1 */

/*==============================================================*/

create table Entity_1 (

Attribute_1 int not null,

Attribute_2 int null,

constraint PK_ENTITY_1 primary key nonclustered (Attribute_1)

)

go

 

/*==============================================================*/

/* Table: Entity_2 */

/*==============================================================*/

create table Entity_2 (

Attribute_3 int not null,

Attribute_1 int null,

Attribute_4 int null,

constraint PK_ENTITY_2 primary key nonclustered (Attribute_3)

)

go

 

/*==============================================================*/

/* Index: Relationship_1_FK */

/*==============================================================*/

create index Relationship_1_FK on Entity_2 (

Attribute_1 ASC

)

go

 

从上面脚本可以看出没有生成需要的外键约束，只是生成了相关索引。

解决方法如下：

1. 打开生成的PDM
2. 将DBMS类型换为MS SQL 2000
3. 在Model菜单中选择References命令，可以看到最后的Implementation被设置为Trigger

 

1. 将Implementation被设置为Declarative(如果没有第二步就不能完成本步)
2. 将DBMS类型换为MS SQL 2008
3. 生成MS SQL 2008数据库生成脚本，能够正常生成需要的外键

    

   /*==============================================================*/

   /* DBMS name: Microsoft SQL Server 2008 */

   /* Created on: 2010/4/5 19:49:25 */

   /*==============================================================*/

    

    

   if exists (select 1

   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')

   where r.fkeyid = object_id('Entity_2') and o.name = 'FK_ENTITY_2_RELATIONS_ENTITY_1')

   alter table Entity_2

   drop constraint FK_ENTITY_2_RELATIONS_ENTITY_1

   go

    

   if exists (select 1

   from sysobjects

   where id = object_id('Entity_1')

   and type = 'U')

   drop table Entity_1

   go

    

   if exists (select 1

   from sysindexes

   where id = object_id('Entity_2')

   and name = 'Relationship_1_FK'

   and indid > 0

   and indid < 255)

   drop index Entity_2.Relationship_1_FK

   go

    

   if exists (select 1

   from sysobjects

   where id = object_id('Entity_2')

   and type = 'U')

   drop table Entity_2

   go

    

   /*==============================================================*/

   /* Table: Entity_1 */

   /*==============================================================*/

   create table Entity_1 (

   Attribute_1 int not null,

   Attribute_2 int null,

   constraint PK_ENTITY_1 primary key nonclustered (Attribute_1)

   )

   go

    

   /*==============================================================*/

   /* Table: Entity_2 */

   /*==============================================================*/

   create table Entity_2 (

   Attribute_3 int not null,

   Attribute_1 int null,

   Attribute_4 int null,

   constraint PK_ENTITY_2 primary key nonclustered (Attribute_3)

   )

   go

    

   /*==============================================================*/

   /* Index: Relationship_1_FK */

   /*==============================================================*/

   create index Relationship_1_FK on Entity_2 (

   Attribute_1 ASC

   )

   go

    

   alter table Entity_2

   add constraint FK_ENTITY_2_RELATIONS_ENTITY_1 foreign key (Attribute_1)

   references Entity_1 (Attribute_1)

   go

    

   （完）