ekaf ekaf - 4 months ago 11
SQL Question

why does junction table of many to many relation in sql implemet both foreign key as primary key?

CREATE TABLE Books(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)




CREATE TABLE Authors(
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthFN VARCHAR(20),
AuthMN VARCHAR(20),
AuthLN VARCHAR(20)
)


CREATE TABLE AuthorBook(
AuthID SMALLINT NOT NULL,
BookID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, BookID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)


here junction table is authorbook
why primary key is (authorid,bookid) is used

why not have a seperate table id and make it primary key?

Answer

why not have a separate table id and make it primary key?

Because you would then need to create a unique index on AuthID, BookID to ensure that duplicate pairs of (AuthID, BookID) are not inserted into this table.

Having many indexes usually slows down inserts and updates and increases the size of the data on disk. BY having a composite primary key, that problem is minimized.

Having said that, certain ORMs most notably Django, does not support multi colum primary keys. Therefor Django's ManyToManyField does indeed have a sequential primary key along with a unque key on (AuthID, BookID) - exactly the alternative method that you asked about.