Is it fine to have foreign key as primary key?

Solution 1:

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.

Solution 2:

Primary keys always need to be unique, foreign keys need to allow non-unique values if the table is a one-to-many relationship. It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship, not a one-to-many relationship. If you want the same user record to have the possibility of having more than 1 related profile record, go with a separate primary key, otherwise stick with what you have.

Solution 3:

Yes, it is legal to have a primary key being a foreign key. This is a rare construct, but it applies for:

• a 1:1 relation. The two tables cannot be merged in one because of different permissions and privileges only apply at table level (as of 2017, such a database would be odd).

• a 1:0..1 relation. Profile may or may not exist, depending on the user type.

• performance is an issue, and the design acts as a partition: the profile table is rarely accessed, hosted on a separate disk or has a different sharding policy as compared to the users table. Would not make sense if the underlining storage is columnar.

Solution 4:

Yes, a foreign key can be a primary key in the case of one to one relationship between those tables