FOREIGN KEY: contains no primary or candidate keys

Question

In SQL Server , I got this error ->

  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.

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.

This url might help you [http://stackoverflow.com/questions/17879735/there-are-no-primary-or-candidate-keys-in-the-referenced-table-that-match-the-re] — Sri, Jan 27 '14 at 12:33
Your foreign keys in `Posts` don't make sense. How can a single column (`Posts.ID`) reference *two* different tables at the same time? I also think you want them the other way round `PostsTags references Posts` and `Comments references Posts` not the way you wrote it. — , Jan 27 '14 at 12:46
post with comments related one to many; post tags associated with many-to-many, as you can not directly, added a table connection. This problem occurs in 2013 visual studio, in 2010 I did the same with no problems. — user3240336, Jan 27 '14 at 12:51
I agree with @a_horse_with_no_name - all of these foreign keys look like you're creating them the wrong way around. — Damien_The_Unbeliever, Jan 27 '14 at 13:07
I'm trying to create a blog using Visual Studio 2013, there is a post he has tags and comments to the post, again in 2010, I did it using relationships. in 2013 no relationships. because trying to write code, seeing as it makes no. again sorry for the bad English. The fact that I do I do not doubt it, but that is how I do have errors. — user3240336, Jan 27 '14 at 13:24

score 3 · Accepted Answer · answered Jan 27 '14 at 13:55

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)

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.

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.

FOREIGN KEY: contains no primary or candidate keys

1 Answers1