Creating a parent and child table, does child need its own primary id column?

Question

When we create parent-child tables, does the child table need its own primary key, or should the foreign key of the parent be used as the primary key?

Example:

I have a table that stores users, which is defined like:

CREATE TABLE users (
    'id' bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    'username' varchar(32),
    PRIMARY KEY ('id'))

I want to make a second table that stores some more information about a user, like their favorite animals. Should that table have its own 'id' column as well as a separate foreign key for the linked user?:

CREATE TABLE users_extra_info (
    'id' bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    'fk_user_id' bigint(20) UNSIGNED NOT NULL,
    'fav_mammal' varchar(32),
    'fav_reptile' varchar(32),
     ...
    PRIMARY KEY ('id'),
    KEY 'fk_user_id' ('fk_user_id'))

or do you usually just drop the 'id' column since the foreign key has to be unique?:

CREATE TABLE users_extra_info (
    'fk_user_id' bigint(20) UNSIGNED NOT NULL,
    'fav_mammal' varchar(32),
    'fav_reptile' varchar(32),
     ...
    PRIMARY KEY ('fk_user_id'))

Thanks

Answer 1

As a matter of principle, not related to other tables, each table should have a PRIMARY KEY in order for you to be able to distinguish one row from another. While it's not always necessary to absolutely identify individual rows that is often a requirement.

In the event of a true one-to-one relationship (or a one-to-zero-or-one relationship, which also occurs), because the foreign key is necessarily unique it can also be used as the primary key of the subsidiary table. There is absolutely no reason at all to introduce a second unique column in that case.

However, one-to-one and one-to-zero-or-one relationships are less common than one-to-many relationships. In that more common case, you cannot use only the foreign key columns as the primary key since they are not unique in the child table. In this case you can choose either to introduce an additional integer key or created a composite primary key using the foreign key column(s) and one or more other columns that, together, are guaranteed to be unique.