CODEWITHSUNDEEP
sql-server
A191919
A191919

Reputation: 3442

relationship many to many

I want to create database with relationship many to many. But i have error

SQL71516 :: The referenced table '[dbo].[BookAuthors]' 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. SQL71516 :: The referenced table '[dbo].[BookAuthors]' 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.

How to fix this?

CREATE TABLE [dbo].[BookAuthors] (
    [Book]   INT NOT NULL,
    [Author] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Book] ASC,[Author] ASC)
);

CREATE TABLE [dbo].[Books] (
    [Id]       INT            NOT NULL,
    [Title]    NVARCHAR (MAX) NULL,
    [Price]    MONEY          NULL,
    [Category] INT            NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    FOREIGN KEY ([Id]) REFERENCES [dbo].[BookAuthors] ([Book])
);

CREATE TABLE [dbo].[Authors] (
    [Id]   INT        NOT NULL,
    [Name] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id]), 
    FOREIGN KEY ([Id]) REFERENCES [dbo].[BookAuthors] ([Author])
);

Upvotes: 0

Views: 78

Answers (2)

Gehan Fernando
Gehan Fernando

Reputation: 1267

The Following Image Shows you the Table Structure.

Table Structure

Upvotes: 0

Nemanja Todorovic
Nemanja Todorovic

Reputation: 2800

You crated in you BookAuthors composite primary key which consists of two columns: Book and Author. When referencing it, you need to reference both columns. Other solution is to make in BookAuthors third column (identity maybe) which would be primary key and reference that one.

Upvotes: 2

Related Questions