Migra Migra - 2 months ago 7
SQL Question

Relation from one table to another with two columns in the primary key in SQL Server

I am trying to make a relation from a table to another like the following :

Books

IdBook (primary)
SerialNumber (primary)
NameBook


The other table is :

Qtt

IdQtt (primary)
IdBook
Qtt


How can I make a relation only between
Qtt.IdBook
and
Books.IdBook
?

Answer

You meant to create a FOREIGN KEY relationship between the tables on that column like

CONSTRAINT FK_idbook FOREIGN KEY (IdBook)     
    REFERENCES Books (IdBook)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE 

But that will not work since you have composite PK in your Books table on IdBook, SerialNumber and thus you need another key column in your Qtt table to refer to both PK column else it would be a PFD (partial functional dependency)

constraint FK_book FOREIGN KEY (IdBook,IdQtt) references Books (IdBook,SerialNumber)
Comments