159

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?

informatik01
  • 16,038
  • 10
  • 74
  • 104
Duc Tran
  • 6,016
  • 4
  • 34
  • 42
  • 3
    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). – Raphaël Althaus Jun 11 '12 at 15:23
  • in you case its better to keep both keys there is no harm in keeping both. – vidur punj Jan 25 '22 at 10:15

9 Answers9

204

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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • 119
    A composite primary key that consists of two foreign keys is also perfectly fine for implementing many-to-many relationships. –  Sep 24 '14 at 07:14
  • 2
    @rezadru: Far be it for me to disagree with rightfold, but a surrogate key is almost always a better choice. – Robert Harvey Jun 03 '19 at 15:37
  • 9
    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 Jan 24 '20 at 16:57
  • 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. – Pablo Alexis Domínguez Grau Oct 29 '20 at 22:18
64

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.

kotekzot
  • 1,518
  • 1
  • 14
  • 23
23

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.

  • 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. – blindguy Jan 24 '20 at 17:08
  • 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. – dhill Jun 02 '22 at 15:58
8

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

Riaj Ferdous
  • 863
  • 7
  • 5
  • 9
    this is useful also for supertype-subtype design. Subtype tables primary key should be foreign key reference to supertype table. – axelio Jan 17 '19 at 15:13
3

It is generally considered bad practise to have a one to one relationship. This is because you could just have the data represented in one table and achieve the same result.

However, there are instances where you may not be able to make these changes to the table you are referencing. In this instance there is no problem using the Foreign key as the primary key. It might help to have a composite key consisting of an auto incrementing unique primary key and the foreign key.

I am currently working on a system where users can log in and generate a registration code to use with an app. For reasons I won't go into I am unable to simply add the columns required to the users table. So I am going down a one to one route with the codes table.

Tshsmith
  • 142
  • 1
  • 8
  • 4
    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. – Teddy Feb 09 '17 at 11:28
  • 1
    @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. – Tshsmith Feb 10 '17 at 09:32
  • 1
    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. – Teddy Feb 10 '17 at 10:04
  • 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. – Tshsmith Feb 10 '17 at 15:40
  • 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. – Marco Rosas Aug 13 '17 at 18:00
  • @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. – Tshsmith Oct 27 '17 at 12:42
  • 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. – user2173353 Nov 27 '19 at 15:50
2

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

Massimiliano Peluso
  • 26,379
  • 6
  • 61
  • 70
  • 2
    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? – Reversed Engineer Aug 04 '15 at 14:06
1

It depends on the business and system.

If your userId is unique and will be unique all the time, you can use userId as your primary key. But if you ever want to expand your system, it will make things difficult. I advise you to add a foreign key in table user to make a relationship with table profile instead of adding a foreign key in table profile.

user3773080
  • 63
  • 1
  • 4
Vincent Cai
  • 11
  • 2
0

Short answer: DEPENDS.... In this particular case, it might be fine. However, experts will recommend against it just about every time; including your case.

Why?

Keys are seldomly unique in tables when they are foreign (originated in another table) to the table in question. For example, an item ID might be unique in an ITEMS table, but not in an ORDERS table, since the same type of item will most likely exist in another order. Likewise, order IDs might be unique (might) in the ORDERS table, but not in some other table like ORDER_DETAILS where an order with multiple line items can exist and to query against a particular item in a particular order, you need the concatenation of two FK (order_id and item_id) as the PK for this table.

I am not DB expert, but if you can justify logically to have an auto-generated value as your PK, I would do that. If this is not practical, then a concatenation of two (or maybe more) FK could serve as your PK. BUT, I cannot think of any case where a single FK value can be justified as the PK.

hfontanez
  • 5,774
  • 2
  • 25
  • 37
0

It is not totally applied for the question's case, but since I ended up on this question serching for other info and by reading some comments, I can say it is possible to only have a FK in a table and get unique values. You can use a column that have classes, which can only be assigned 1 time, it works almost like and ID, however it could be done in the case you want to use a unique categorical value that distinguish each record.

João Ramos
  • 75
  • 1
  • 6