Junction tables in T-SQL
In my database application, when I try t开发者_如何学Co attach a page to a site, I get the following error:
System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(Junc_Page_Site)' because it has no primary key.
I intend a page to be able to be added to multiple sites - by using a junction table.
Below is the schema for the three tables in question (T-SQL). Any advice will be appreciated!
CREATE TABLE Site (
Id INTEGER NOT NULL IDENTITY ,
FkSiteId INTEGER ,
Name NVARCHAR(64) NOT NULL ,
Domain NVARCHAR(128) ,
Theme NVARCHAR(32) ,
ThemeVariation NVARCHAR(32) ,
PRIMARY KEY(Id) ,
FOREIGN KEY(FkSiteId)
REFERENCES Site(Id));
GO
CREATE TABLE Page (
Id INTEGER NOT NULL IDENTITY ,
Title NVARCHAR(64) NOT NULL ,
Description NVARCHAR(256) NOT NULL ,
Keywords NVARCHAR(1024) NOT NULL ,
ScriptName NVARCHAR(64) ,
PRIMARY KEY(Id));
GO
CREATE TABLE Junc_Page_Site (
FkPageId INTEGER NOT NULL ,
FkSiteId INTEGER NOT NULL ,
FOREIGN KEY(FkSiteId)
REFERENCES Site(Id),
FOREIGN KEY(FkPageId)
REFERENCES Page(Id));
GO
You're almost there. You just need to create what's called a "composite primary key", which is to say that you need to tell the database that the conbination of FkPageId and FkSiteId will be unique:
CREATE TABLE Junc_Page_Site (
FkPageId INTEGER NOT NULL ,
FkSiteId INTEGER NOT NULL ,
FOREIGN KEY(FkSiteId)
REFERENCES Site(Id),
FOREIGN KEY(FkPageId)
REFERENCES Page(Id),
PRIMARY KEY (FkPageId, FkSiteId));
精彩评论