问题描述
我试图在一个表的两列上创建一个外键,以指向另一个表的同一列,但是似乎出现了错误。
I'm trying to create a foreign key on two columns of a table to point to the same column of another table, but I seem to get an error.
我正在将SQL Server与Microsoft SQL Server Management Studio一起使用。
I'm using SQL Server with Microsoft SQL Server Management Studio.
这是我的工作:
CREATE TABLE House
(
hid INTEGER PRIMARY KEY,
hname VARCHAR(32) NOT NULL,
address VARCHAR(128) NOT NULL,
);
CREATE TABLE Room
(
sid INTEGER,
hid INTEGER FOREIGN KEY REFERENCES House(hid),
rname VARCHAR(32) NOT NULL,
CONSTRAINT sid_pk PRIMARY KEY(sid, hid)
);
CREATE TABLE Seat
(
hid INTEGER,
sid INTEGER,
plid INTEGER,
row INTEGER NOT NULL,
seat INTEGER NOT NULL,
CONSTRAINT sid_fk
FOREIGN KEY (hid, sid) REFERENCES Room(hid, sid), <-- Here's the error
CONSTRAINT plid_pk
PRIMARY KEY (hid, sid, plid),
UNIQUE(hid, sid, row, seat)
);
我不断收到错误1776,说房间表中没有主键。 ..
And I keep getting "Error 1776" saying that there is no primary key in the room-table...
推荐答案
根据来自@ hogan..com的评论作为社区Wiki发布,这样就不会丢失
as per comments from @hogan..posting as community wiki,so that this is not lost
在 Room
表中,主键为(sid,hid)
..但是在座位
表中,您引用的是新主键 Room(hid,sid)
In the Room
table ,primary key is (sid, hid)
..But in seat
table,you are referencing a new primary key Room(hid, sid)
因此更改您的房间
表约束定义,如下所示
so change your room
table constraint definition like below
CONSTRAINT sid_fk
外键(hid,sid)参考资料室(sid,hid)
这篇关于SQL Server:在多列上添加外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!