CODEWITHSUNDEEP
mysqlerd
melkawakibi
melkawakibi

Reputation: 881

Can a table use the FK of the parent table as a PK

I'm having an argument with a coworker about whether a child table can use the primary key of its parent table. This considers a 1 to 1 relation.

For example:

enter image description here

Upvotes: 0

Views: 66

Answers (1)

Hyrein
Hyrein

Reputation: 391

From academic point of view a Primary key must fit all these points:

  • unique
  • minimal
  • all PK-fields are non-null

Foreign keys are references to other table's primary keys. So they must exactly include the fields that the referenced PK has. And this does not violate normalization since the PK must be minimal (from academic point of view).

When you have a primary key with 2 or more columns and you think it is enough to reference its rows with only one column then your PK is not valid because it is not minimal. When it is valid and you need all these columns to fullfill uniqueness then you also need all these column in the foreign key.

DBMS allow you to define non-minimal PKs but that's then the point where you're leaving the academic concepts.

Upvotes: 2

Related Questions