Search code examples
databaseforeign-keysprimary-key

Is it fine to have foreign key as primary key?

I have two tables:

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

Currently I'm using this approach: each Profile record has a field named "userId" as foreign key which links to the User table. When a user registers, his Profile record is automatically created.

I'm confused with my friend suggestion: to have the "userId" field as the foreign and primary key and delete the "profileId" field. 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.