我正在使用SQL Server 2008。我在尝试创建表"Dossier_Financement"时收到错误:
create table Dossier_Financement
(
ID_Reunion integer foreign key references Reunion(ID_Reunion),
ID_Dossier integer foreign key references Dossier(ID_Dossier),
Decision varchar(20),
Motif text,
Montant_Retenu decimal(6,2),/* montant accorder */
Duree_Retenu smallint,/*nb jours accorder */
Nom_Giac varchar(50) foreign key references GIAC(Nom_Giac),
primary key(ID_Dossier,Nom_Giac,ID_Reunion)
)
GO
这是两个表:
create table Reunion
(
ID_Reunion integer ,
Date_Reunion datetime,
ID_Membre integer,/*jquery*/
Type_Reunion varchar(20),
Nom_Giac varchar(50),
foreign key(ID_Membre,Nom_Giac) references Membre(ID_Membre,Nom_Giac),
primary key(ID_Reunion,Nom_Giac)
)
GO
create table Dossier_Financement
(
ID_Reunion integer foreign key references Reunion(ID_Reunion),
ID_Dossier integer foreign key references Dossier(ID_Dossier),
Decision varchar(20),
Motif text,
Montant_Retenu decimal(6,2),/* montant accorder */
Duree_Retenu smallint,/*nb jours accorder */
Nom_Giac varchar(50) foreign key references GIAC(Nom_Giac),
primary key(ID_Dossier,Nom_Giac,ID_Reunion)
)
GO
"团聚"正常执行没有任何问题,但是在尝试创建第二个表时出现此错误:
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'Reunion' that match the referencing column list in the foreign key 'FK__Dossier_F__ID_Re__5629CD9C'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
根据实际的模型需求/要求,一种解决方案是引用 Key,这需要所有部件(请注意,Nom_Giac
已添加到 FK 定义中):
create table Dossier_Financement
(
...
foreign key(ID_Reunion,Nom_Giac) references Reunion(ID_Reunion,Nom_Giac),
)
根据 Mark M 的答案,另一种解决方案是将ID_Reunion列设为键(请注意,Nom_Giac
已从 PK 定义中删除):
create table Reunion
(
...
primary key(ID_Reunion)
)
这将使ID_Reunion成为键(实际上是主键),然后可以使用foreign key references Reunion(ID_Reunion)
引用该键。
祝您编码愉快!
只能使用所引用表的完整主键创建外键。 第二个表尝试仅使用团聚表主键的一半来创建外键。
在要在其上创建索引的列上创建唯一索引:
CREATE UNIQUE NONCLUSTERED INDEX [UX_Index] ON dbo.[Table]([Column1],[Column2])