This post has already been read 12675 times!

Have you ever designed an ER Diagram in MySQL Workbench and wondered, just what is the difference between “Identifying” and “Non-Identifying” relationships? Non-Identifying Relationships have dotted lines, whereas Identifying Relationships have solid lines in MySQL Workbench. The difference is subtle but worth knowing.

When you create an identifying relationship, the primary key of the child table becomes a foreign key in the parent table. In non-identifying relationships, the primary key of the child table is still included as a foreign key, but it doesn’t get to participate as a primary key. Hence, it is non-identifying. Here is an example for a 1:n relationship:

IdentifyingNonIdentifyingRelationships

Taking this a bit further, you’ll notice that in identifying relationships, it is really not possible to insert a record into the parent table without referencing the child table. So if you had a 1:n relationships between Brands and Products, where a Brand can have many Products, but a Product only has 1 Brand.

If that relationship is identifying, we’d have the Brand Primary Key as part of the Primary Key in Products. Therefore, each Product needs a Brand to be uniquely identified.
If that relationship is non-identifying, the Brand Primary Key is still a Foreign Key in the Product Table, but it’s not part of the primary key. We can identify Products uniquely by Product ID alone.

Hope this shed some light on the issue.

Comments are closed.

Post Navigation