Search code examples
databaseforeign-keysprimary-key

Is it fine to have foreign key as primary key?

Tables:

User (userId, username, password)
Profile (profileId, userId, gender, dateofbirth, ...)

Each Profile record has a field userId as foreign key which links to User. When a user registers, their Profile record is automatically created.

My friend suggests I have Profile userId as foreign and primary key and delete profileId.

Which approach is better?

Solution

  • Foreign keys are almost always "Allow Duplicates," which would make them unsuitable as Primary Keys.

    Instead, find a field that uniquely identifies each record in the table, or add a new field (either an auto-incrementing integer or a GUID) to act as the primary key.

    The only exception to this are tables with a one-to-one relationship, where the foreign key and primary key of the linked table are one and the same.