Is it fine to have foreign key as primary key?

Question

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 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

Solution 5

I would not do that. I would keep the profileID as primary key of the table Profile

A foreign key is just a referential constraint between two tables

One could argue that a primary key is necessary as the target of any foreign keys which refer to it from other tables. A foreign key is a set of one or more columns in any table (not necessarily a candidate key, let alone the primary key, of that table) which may hold the value(s) found in the primary key column(s) of some other table. So we must have a primary key to match the foreign key. Or must we? The only purpose of the primary key in the primary key/foreign key pair is to provide an unambiguous join - to maintain referential integrity with respect to the "foreign" table which holds the referenced primary key. This insures that the value to which the foreign key refers will always be valid (or null, if allowed).

http://www.aisintl.com/case/primary_and_foreign_key.html

View more solutions

Comments

[Raphaël Althaus]

Entity Framework generates that (with code first) for zeroOrOne (and one)-to- one relations. So... it's possible. Is it the best way... That's another question. But it's valid. I never did that while creating my own databases (but I even never thought to that).

[vidur punj]

in you case its better to keep both keys there is no harm in keeping both.

[Admin]

A composite primary key that consists of two foreign keys is also perfectly fine for implementing many-to-many relationships.

[Reversed Engineer]

Maybe - if you have the FK constraint between User.UserID and Profile.UserID, then it would be strongly recommended to have an index on Profile.UserID. Why not make that the primary clustered index on table Profile, saving a second index and a whole lot of unnecessary work for the database engine?

[Teddy]

I agree with you mostly that there are many advantages to having all the data in the same table as additional columns. Although w.r.t this.. "you could just have the data represented in one table and achieve the same result".. : having a separate table can be useful, for example here if the profile table entry is optional. For example, every bank customer might not have a internet banking registration. In that case the IB registration table could be used to restrict other tables from having further child records. Again, here it could be done with a new PK for the IB registration table also.

[Tshsmith]

@Teddy Likewise I mostly agree with what you said. However, in the original question they state "...his Profile record is automatically created..." implying that the Profile table isn't optional. In a situation where the profile table was optional then yes having it as a separate table is doable. But then again, they could just use nullable columns in the same table.

[Teddy]

Using a separate second table, we can prevent entry in a third table which is only allowed for people who have an entry in the second table.

[Tshsmith]

Absolutely, but if we merge the 1 to 1 tables we can prevent people with null values from accessing the third (technically second now) table. But the question OP asked contains the the line " ...when signing up.. ...his profile record is automatically created...", Making this redundant.

[Marco Rosas]

My primary reason for considering this is that in data warehousing, it is good practice to separate fact and dimension tables. The separate fact and dimension tables are useful cues when working with software such as PowerPivot, PowerBI, and Tableau.

[Tshsmith]

@MarcoRosas, You keep the data used for the live system and the data for warehousing separate in most cases. The script you use to migrate the data from the live system to the warehouse system would just need to take this into account.

[axelio]

this is useful also for supertype-subtype design. Subtype tables primary key should be foreign key reference to supertype table.

[Yinda Yin]

@rezadru: Far be it for me to disagree with rightfold, but a surrogate key is almost always a better choice.

[user2173353]

If you have all columns in one table, the table might grow larger in size, causing performance issues on queries on that table. Right? So, performance-wise, it makes sense to split it into two tables sometimes.

[blindguy]

Nothing about a foreign key dictates that it is a 1 to many (or "allow duplicates" as written). Key constraints and uniqueness are two separate concepts in a database and can easily be mixed just as easily as adding an index (which would be a third separate concept).

[blindguy]

There would be a negative performance if the tables were joined often, which leads to the normal recommendation that 1 table is better. In some cases the data is always accessed separately, not joined, and there can be a organizational benefits to having two tables with a 1:1 relationship.

[Pablo Alexis Domínguez Grau]

I also agree with @RobertHarvey - surrogate keys make a better choice in some cases as well. For example, you could have an intermediate table that maps out a many-to-many relationship, whose primary key is used as part of another table. By NOT HAVING a surrogate key, all the foreign keys marked as the Primary Key will end up bloating your other tables. However, if you HAVE a surrogate key, all that is passed down is one key. In my opinion, it is more simple and efficient.

[dhill]

One other use I'm looking for is model inheritance with a common key space. In my case I want to be able to register Events on all Inventory subclasses with a simple foreign key. I want each key in Inventory to be used as foreign key in exactly one table.