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

Question

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?

You can do it either way. When wider datatypes are in place, such as varchars, a thin PK is preferable for scaling. For special use cases of compositing, you certainly need to have the int id as seen [here](http://stackoverflow.com/a/38340726) in special use cases. — Drew, Jul 22 '16 at 15:23
Why add a surrogate key to what are already surrogate keys? Unless the resolution table is to be a master to another detail, there is no reason to add yet another meaningless key to the table. As an industry we've gone crazy with surrogates, IMHO. — T Gray, Jul 22 '16 at 15:27

score 0 · Accepted Answer · edited Sep 14 '17 at 12:49

0

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.

edited Sep 14 '17 at 12:49

Dunatotatos

1,706
15
25

answered Jul 22 '16 at 14:41

e4c5

52,766
11
101
134

Please join me in [Campaigns](http://chat.stackoverflow.com/rooms/95290/campaigns) to try to work on `mysql` Docs. Thx e4c5 – Drew Jul 23 '16 at 03:55
gladly will do @Drew – e4c5 Jul 23 '16 at 04:13

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

1 Answers1