MySQL - Using foreign key as primary key too
Asked Answered
V

1

29

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?

Vandervelde answered 20/1, 2012 at 23:9 Comment(1)
Similar question (but not a duplicate): How to create foreign key that is also a primary key in MySQL?Dittany
W
33

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.

Wartow answered 20/1, 2012 at 23:13 Comment(4)
Thanks. In my case it's the first scenario you mentioned, plus the fact that table 2 has a lot of columns, and there are also tables 3, 4 etc. which are in similar relation to table 1, so putting all columns would be too much.Vandervelde
One situation in which this makes sense is if you're using class table inheritance and table 2 represents a subclass of table 1. I guess this is a special case of your first bullet point.Paramecium
Another reason to do it would be if you have two different tables, Table A and Table B, that share columns in Table C. For example you have a Table called Contacts, another called Employees, and another called People. People has firstname, lastname, etc. and Contacts and Employees are both representing people.Kaminski
I used this scenario to build a table with user settings: all users had the same set of settings but could change values.Wordbook

© 2022 - 2024 — McMap. All rights reserved.