Question

Im 使用 SQL 服务器 2008 。当我试图创建表格 Dossier_ finance 时, 我收到错误 :

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.

Answer 1

视实际模型需要/要求而定,一个解决办法是提及键,这需要all 部件(注意在定义中添加 Nom_Giac ):

create table Dossier_Financement 
(
    ...
    foreign key(ID_Reunion,Nom_Giac) references Reunion(ID_Reunion,Nom_Giac),
)

根据 Mark M s 的回答,另一个解决方案是使 ID_ reunion 列成为关键(注意从 PK 定义中删除 < code> Nom_ Giac ):

create table Reunion 
(
    ...
    primary key(ID_Reunion)
)

这将使得ID-reunion成为密钥(实际上主要密钥),然后可以用 外国密钥Reunion(ID-Reunion) 引用。

快乐的编码!

Answer 2

您只能使用您正在引用的表格的完整主键来创建外国密钥。您的第二张表格试图仅使用留尼汪表格的一半主键来创建外国密钥。

Answer 3

在您想要创建索引的列上创建唯一的索引 :

CREATE UNIQUE NONCLUSTERED INDEX [UX_Index] ON dbo.[Table]([Column1],[Column2])

友情链接