MySQL - Using foreign key as primary key too

mysql foreign-keys primary-key foreign-key-relationship primary-key-design
CodeVirtuoso picture CodeVirtuoso · Jan 21, 2012 · Viewed 17.6k times · Source

I have table 1 with a primary key user_id and table 2 where user_id is a foreign key.

Only 1 record per user_id can exist in table 2, and no record can exist without it.

QUESTION: Can user_id in table 2 be both foreign and primary key at the same time, and if yes, is it a good idea, what are pros/cons?

Answer

Jon picture Jon · Jan 21, 2012

Yes, you can do this (and you should, from a database design point of view).

However, consider what it means if user_id is the primary key on table 2. You are in effect saying that each row in table 2 corresponds to a user, but you already have a table where each row corresponds to a user: table 1. This raises the question "why then don't you put all data of table 2 into nullable columns in table 1?". After all, having two tables means you will have to make two queries to get this data instead of one.

Now there are some scenarios where this practice might be a good idea:

  • if you have lots of users but only a few rows in table 2, perhaps the query on table 2 will be only performed rarely; at the same time, you gain storage space and modification speed on table 1
  • it might be possible in the future for the primary key of table 2 to change, while the foreign key remains; if you put all the data in table 1, this modification would most likely break your database model

It can be a good idea, but it depends on the particulars of your application.

Related questions

How to create foreign key that is also a primary key in MySQL?

This should be a fairly straightforward question, but I'm unable to find an easy answer. How do you create a foreign key that is also a primary key in MySQL? Here's my current attempt: CREATE TABLE Sale( sale_id CHAR(40), …

Read More
mysql Multiple Foreign Keys in a Table to the Same Primary Key

I have a table user with userID as the primary key. I have another table called Friends. In the Friends table, I have two Users as friends represented by the columns UserID and FrndID where both UserID and FrndID should …

Read More
Changing MySQL primary key when foreign key contraints exist

I have two already-existing tables which look (in part) roughly like this: CREATE TABLE parent ( old_pk CHAR(8) NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE child ( parent_key CHAR(8), FOREIGN KEY (parent_key) REFERENCES parent(old_pk) ON UPDATE …

Read More