FOREIGN KEY：不包含主键或候选键

In SQL Server , I got this error ->

在SQL Server中,我收到此错误 - >

  SQL71516 :: The referenced table '[dbo].[PostsTags]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column
 is a computed column, it should be persisted.

I don't understand why dint works foreign key, without them table created without problems, but I need a relationship between tables, in this location.I looked at other similar questions, but the answers were not found.

我不明白为什么dint工作外键,没有他们创建没有问题的表,但我需要表之间的关系,在这个位置。我看了其他类似的问题,但没有找到答案。

CREATE TABLE [dbo].[PostsTags] (
    [PostId] INT NOT NULL,
    [TegId]  INT NOT NULL,
    CONSTRAINT [PK_PostsTags] PRIMARY KEY CLUSTERED ([PostId] ASC, [TegId] ASC)
);


CREATE TABLE [dbo].[Comments] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [PostId]   INT            NOT NULL,
    [DateTime] DATETIME       NOT NULL,
    [Name]     NVARCHAR (64) NOT NULL,
    [Body]     NVARCHAR (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Tags] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (64) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Tags_PostsTags] FOREIGN KEY ([Id]) REFERENCES [PostsTags]([TegId]) ON DELETE CASCADE 
);

CREATE TABLE [dbo].[Posts] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Title]    NVARCHAR (128) NOT NULL,
    [DateTime] DATETIME       NOT NULL,
    [Body]     NVARCHAR (MAX) NOT NULL,
    [Avtor]     NVARCHAR (64)  NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [Post_Tag] FOREIGN KEY ([Id]) REFERENCES [PostsTags]([PostId]) ON DELETE CASCADE,
CONSTRAINT [Post_Comment] FOREIGN KEY ([Id]) REFERENCES [Comments]([PostId]) ON DELETE CASCADE,
);

Sorry for bad English.

抱歉英语不好。

1 个解决方案

#1

What you want is this, I'm sure:

你想要的是这个,我敢肯定:

CREATE TABLE [dbo].[Tags] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (64) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Posts] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Title]    NVARCHAR (128) NOT NULL,
    [DateTime] DATETIME       NOT NULL,
    [Body]     NVARCHAR (MAX) NOT NULL,
    [Avtor]     NVARCHAR (64)  NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[PostsTags] (
    [PostId] INT NOT NULL,
    [TagId]  INT NOT NULL,
    CONSTRAINT [PK_PostsTags] PRIMARY KEY CLUSTERED
           ([PostId] ASC, [TagId] ASC),
    CONSTRAINT [FK_PostsTags_Tags] FOREIGN KEY ([TagId])
           REFERENCES [Tags]([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTags_Posts] FOREIGN KEY ([PostId])
           REFERENCES [Posts]([Id]) ON DELETE CASCADE 
);

CREATE TABLE [dbo].[Comments] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [PostId]   INT            NOT NULL,
    [DateTime] DATETIME       NOT NULL,
    [Name]     NVARCHAR (64) NOT NULL,
    [Body]     NVARCHAR (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Comments_Posts] FOREIGN KEY ([PostId])
          REFERENCES [Posts](Id) ON DELETE CASCADE
);

That is - you can freely insert into Tags or Posts without consideration of any other tables or data within them. Once there's a row in Posts, you can start adding rows to Comments, provided that the PostId column contains a value that already exists in the Posts table's Id column (Comments references Posts)

也就是说 - 您可以*地插入标签或帖子而不考虑其中的任何其他表格或数据。一旦Posts中有一行,您可以开始向Comments添加行,前提是PostId列包含Posts表的Id列中已存在的值(Comments references Posts)

Further, once there are rows in both Posts and Tags, only then can you insert rows into PostTags, with again constraints on what values are valid in that table being based on current rows in Posts and Tags.

此外,一旦Posts和Tags中都有行,只有这样才能在PostTags中插入行,并再次限制该表中基于Posts和Tags中的当前行有效的值。

Finally, I've left your CASCADE options set as in your original. If someone deletes a row from Posts then all rows in Comments that relate to that post are deleted. Similarly, any rows in PostsTags that relate to the post are deleted. If someone deletes a row from Tags then all rows in PostsTags that references that tag are removed.

最后,我将CASCADE选项设置为原始设置。如果有人从帖子中删除了一行,那么评论中与该帖子相关的所有行都将被删除。同样,PostsTags中与帖子相关的任何行都将被删除。如果有人从Tags中删除了一行,那么PostsTags中引用该标记的所有行都将被删除。