CODEWITHSUNDEEP
mysqldatabase-designrelationship
SercioSoydanov
SercioSoydanov

Reputation: 1400

Identifying and Non-Identifying relationships from programming perspective

So, I've been reading questions and answers about identifying and non-identifying relationships in a database design.

I understand that if a child row logically cannot be identified without its parent row, then it is an identifying relationship.

I've been thinking about it for a while. When looking at it from real-life logical perspective, it makes perfectly sense. But I couldn't quite grasp what difference does it make from technical point of view of database engine.

So, what difference does it make if I define the child row as not-null and create a restricting foreign key to the parent row, instead of identifying the id of parent row as a part of child row's primary key?

Upvotes: 1

Views: 83

Answers (1)

nvogel
nvogel

Reputation: 25526

In a relational/SQL database it makes no practical difference. The identifying / non-identifying concept exists in ER modelling primarily as a way of explaining semantic aspects of the business domain, i.e. business rules about the preferred means of identification and mandatory relationships between entities.

There also exists a practice among some ER modellers that referencing attributes (i.e. "foreign keys") are shown on a diagram only when they are also required for identification purposes (i.e. "primary key" attributes). When the references are "non-identifying" the theory goes that they are implied by the existence of a relationship line and don't need to be shown in the referencing entity at all. That convention rarely seems to be observed these days (at least in my experience) but it may explain why the supposed significance of identifying relationships came about.

Upvotes: 1

Related Questions