T-SQL 中的连接表

发布于 2024-09-18 07:24:00 字数 1052 浏览 10 评论 0原文

在我的数据库应用程序中,当我尝试将页面附加到站点时,出现以下错误:

System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(Junc_Page_Site)' because it has no primary key.

我希望能够通过使用联结表将页面添加到多个站点。

下面是相关三个表的架构 (T-SQL)。任何建议将不胜感激!

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

In my database application, when I try to 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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

紅太極 2024-09-25 07:24:00

你快到了。您只需要创建所谓的“复合主键”,也就是说您需要告诉数据库 FkPageId 和 FkSiteId 的组合将是唯一的:

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));

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));
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文