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,
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)
why not have a separate table id and make it primary key?
Because you would then need to create a unique index on
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.