SQL. How I can create 2 or more foreign keys in a table?

Question

I want to create a table, that stores available books based on a QUANTITY value. If quantity > 0 this book is available. But I have an error:

Msg 1776, Level 16, State 0, Line 48
There are no primary or candidate keys in the referenced table 'BOOK_INFO' that match the referencing column list in the foreign key 'FK_QUANTITY'.
Msg 1750, Level 16, State 1, Line 48
Could not create constraint or index. See previous errors.

Code (the problem is table 'AVAILABLE_BOOKS'):

CREATE TABLE BOOKS
(
    ID INT IDENTITY PRIMARY KEY,
    BOOK_NAME VARCHAR(30) NOT NULL,
    BOOK_AUTHOR VARCHAR(30) NOT NULL
)

CREATE TABLE BOOK_INFO
(
    BOOK_ID INT PRIMARY KEY FOREIGN KEY REFERENCES BOOKS(ID),
    QUANTITY INT DEFAULT 1 NOT NULL,
    PRICE DECIMAL(5,2) NOT NULL,
    CONSTRAINT CHECK_PRICE CHECK (PRICE > 0),
    CONSTRAINT CHECK_QUANTITY CHECK (QUANTITY > 0)
)

CREATE TABLE ABAILABLE_BOOKS
(
    BOOK_ID INT PRIMARY KEY,
    QUANTITY INT DEFAULT 1 NOT NULL,
    CONSTRAINT FK_BOOK_ID FOREIGN KEY (BOOK_ID) REFERENCES BOOKS(ID),
    CONSTRAINT FK_QUANTITY FOREIGN KEY (QUANTITY) REFERENCES BOOK_INFO(QUANTITY),
    CONSTRAINT CHECK_QUANTITY CHECK (QUANTITY > 0)
)

I was trying to change field declaration (QUANTITY INT -> QUANTITY INT DEFAULT 1 NOT NULL) , and many other things that didn't help.

The error message tells you, "There are no primary or candidate keys in the referenced table 'BOOK_INFO' that match". So make one. This is a faq. Research is expected & duplicate questions should not be asked or answered. Many many Q&A answering this question are immediately at a google of the error message. [ask] [answer] [Help] — philipxy, Feb 19 '23 at 05:33
Why do you tag this question with MySQL which is completely different to SQL Server?! — Georg Richter, Feb 19 '23 at 06:16
I do not believe QUANTITY is an appropriate column on which to define a foreign key. It is an attribute, that may have repeated values in both the AVAILABLE_BOOKS and BOOKS_INFO tables. (How many book titles do you anticipate will have just one or two copies?) Also, do you expect that the available books quantity will always equal the BOOK_INFO quantity? If so perhaps what you really want is a view, not another table. — T N, Feb 20 '23 at 19:08

score 0 · Answer 1 · answered Feb 19 '23 at 05:19

You need to create an UNIQUE index on BOOK_INFO(QUANTITY)

CREATE TABLE BOOKS ( 
    ID INT IDENTITY PRIMARY KEY, 
    BOOK_NAME VARCHAR(30) NOT NULL, 
    BOOK_AUTHOR VARCHAR(30) NOT NULL 
)

CREATE TABLE BOOK_INFO ( 
    BOOK_ID INT PRIMARY KEY FOREIGN KEY REFERENCES BOOKS(ID), 
    QUANTITY INT DEFAULT 1 NOT NULL, 
    PRICE DECIMAL(5,2) NOT NULL, 
    CONSTRAINT CHECK_PRICE CHECK (PRICE > 0), 
    CONSTRAINT CHECK_QUANTITY CHECK (QUANTITY > 0) 
)
CREATE UNIQUE INDEX IX_BOOK_INFO_QUANTITY ON BOOK_INFO(QUANTITY)

CREATE TABLE ABAILABLE_BOOKS
(
    BOOK_ID INT PRIMARY KEY,
    QUANTITY INT DEFAULT 1 NOT NULL,
    CONSTRAINT FK_BOOK_ID FOREIGN KEY (BOOK_ID) REFERENCES BOOKS(ID),
    CONSTRAINT FK_QUANTITY FOREIGN KEY (QUANTITY) REFERENCES BOOK_INFO(QUANTITY),
    CONSTRAINT ABAILABLE_BOOKS_CHECK_QUANTITY CHECK (QUANTITY > 0)
)

I suspect the database will have significant limitations if there is a unique index on book quantity. — T N, Feb 20 '23 at 19:09
The only way to create a FK is to refer to a table via its PK (which is UNIQUE by definition) or UNIQUE key, I do agree with you on a design point of view. But the response as generic answer stands — Luis LL, Feb 21 '23 at 05:33

SQL. How I can create 2 or more foreign keys in a table?

1 Answers1