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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[database] Is it fine to have foreign key as primary key?

Examples related to database

Examples related to foreign-keys

Examples related to primary-key