can two tables have same primary key and at the same time this primary key is a foreign key for one of them?

Question

I have a table

user
  id (PK)
  email 
  username 
  password 
  status
  user_role
  profile_image
  created_at

and another table:

healthdata
  id (PK)
  age
  gender
  height 
  goal
  weight
  g_weight
  BMR 
  total_calories 
  a_level 
  date

The relationship between these two tables is one-to-one 'each user should have one health data'

I do not know if it's right to use the primary key (id) in healthdata table as a foreign key that references id in user table so that healthdata.id becomes a foreign and a primary key at the same time.

Answer 1

Can they? Yes. It's entirely possible to create the 1:0-1 relationship as you've demonstrated¹.

But if you need that to be a 1-1 relationship, that breaks the relational model because the primary key of the child cannot be NULL, yet it is entirely possible to have a transaction that only inserts into the parent table. So, you must use stored procedures for inserts/updates/delete so everything is handled as if it were a single logical entity and changes are made within a single transaction.

Should they? Depends. This is a physical (not logical) consideration and the only reasons you should do this are:

1. Performance - if the total size of the entity (in bytes) is large and a good portion of that data is read infrequently, it can improve performance to have the less-frequently accessed data in its own table to reduce the I/O for transactions that don't need to read that data.

2. Backups- if you need to have a different policy for certain data elements (or need to exclude them from backups entirely), you have some options if the data is stored in a separate table.

If there are security concerns around access, you could partition your data into separate tables and assign permissions that way, but you'd be able to achieve the same thing with views/stored procedures and denying direct access to the table.

---

¹ I would question if HealthData wasn't actually a series of measurements with the primary key being (UserId,Date) or something similar.

Answer 2

Yes, but at most one can be AUTO_INCREMENT, else they would be fighting over how to number them.

No, in the sense that is rarely wise to have two tables instead of one. There are exceptions; what is your reason for wanting two? Perhaps...

• You want to give access to the health data to fewer user logins?
• Or the health data is optional and not available for all people. This would lead to fewer rows in one vs the other, and LEFT JOIN to get NULLs when you do Join them.

It seems quite dangerous to have both FOREIGN KEYs do any CASCADEing.

FOREIGN KEYs provide certain integrity checks, but they are not mandatory when building a schema.