What to choose: primary key or unique for a unique foreign key?

Question

MySQL

Given

Table Field Author
: ID |… |…
Table Book
Field: ID | … |… Table Book Field Author:
BookID
| AuthorID

Relationship:
BookID -> Book.ID
AuthorID -> Author.ID
(-> = foreign key)

Desired behavior: the same book-author can only be specified once.

What is the best way to do this? Set (BookID, AuthorID) as UNIQUE or (BookID, AuthorID) as a composite Primary Key?

NULL is not supposed to be used...

Answer 1

Just in case: make sure the "book - author" is unique. For example, if you mean an online store or a catalog, you need to consider that the same book can be published by different publishers. In addition, even in one publishing house, the author can appear in different guises: "B. Akunin", "Akunin B." "Boris Akunin", etc.

Answer 2

If for an educational project, then it is better as a primary ID.

Answer 3

Better UNIQUE. This will allow you to have a separate simple exact id for the linked table. This usually makes things easier later if you have to work with the linked table in parallel queries outside of serialized transactions. Extra index for int primary key is not a problem.

Answer 4

In any case, there must be a unique identifier.
If we are talking about books, then I would duplicate the uniqueness by ID UNIQUE and the field with ISBN, even if one book is from different publishers, the number will be different

Answer 5

You can add a couple of fields to the BookAuthor table, for example: BookAuthorID and Note :) Well
, it depends on your goals.