Is it fine to have foreign key as primary key?

database foreign-keys primary-key
Duc Tran picture Duc Tran · Jun 11, 2012 · Viewed 179.8k times · Source

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?

Answer

Robert Harvey picture Robert Harvey · Jun 11, 2012

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.

Related questions

Does the foreign keys automatically get updated as primary table is updated?

Above is my simple database design, just wanted to gain information about how things happen as I'm really new at database. Following are my questions: as I update wall_id in walls table, does the wall_id in wall_categories …

Read More
What are the design criteria for primary keys?

Choosing good primary keys, candidate keys and the foreign keys that use them is a vitally important database design task -- as much art as science. The design task has very specific design criteria. What are the criteria?

Read More
How to remove foreign key constraint in sql server?

I want to remove foreign key from another table so i can insert values of my choice. I am new in databases so please tell me correct sql query to drop or remove foreign key value.

Read More