MySQL Forums
Forum List  »  Newbie

Re: Sakila database, film_actor table. How do we order them?
Posted by: Phillip Ward
Date: August 15, 2022 07:29AM


In first variant I add autoincrement id, as I read somewhere every table need it.

Every Table requires a Primary Key.
What form that Primary Key takes is [still] up for grabs.

These days, it's most common to use a numeric, Surrogate Primary Key (that is meaningless in the Real World). Any other table that references this one only has to specify this one field.

You can also have a Composite Primary key, made up of all the fields needed to make a given record unique which, in this case, would be {film_id, actor_id}.
If another table references this one, then it would have to include both fields, which is another reason the Surrogate Key is so popular.

If you do use a Surrogate Primary Key, then you'll still have to enforce the uniqueness of these two fields, with a [Composite] Unique Index, as in:

create table 
primary key ( id ) 
unique key ( film_id, actor_id )

Regards, Phill W.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.