What to do if weak entity set does not have a primary key?

1.3K    Asked by BenPHILLIPS in SQL Server , Asked on Oct 4, 2022

 I have come across two different definitions of weak entities, which may sometimes be contradictory.


1) Weak entity is an entity which cannot exist without some other (owner) entity.

2) Weak entity does not have a primary key, but rather a partial key, and can be only uniquely identified by combining this partial key with a foreign key from the owner entity.


Which one of these is true? Let us take the example of Customers->Orders relationship, where Orders have a unique OrderID. Here an Order cannot exist without a Customer, however it still has its own primary key. Would it be a strong or a weak entity then?

Answered by Abhishek Shukla

If weak entity set does not have a primary key - The primary key is a way to distinguish one row in a single table from all other rows in that same table. It is not a way to distinguish one row in the context of its associated rows from other tables.

Sometimes a table's primary key consists of a single column. A person's user_id would be an example. Sometimes it is made up of several columns. A location is both latitude and longitude. This is known as a compound key. Sometimes one or more of those columns may also be a foreign key. This is termed a weak entity type.

To take your example - could a single row in the Orders table be distinguished from all other rows by the Order Number alone? Typically, yes. The order number is unique across the whole system. So given order number 8765 we know that's for customer A. This makes Order a strong entity type.

How about the OrderLine table? Given a single order line number, say "1", could we unambiguously find which Order that relates to? Typically no, because order line numbers start again for each Order. OrderLine is therefore a weak entity because its primary key (order number, order line number) requires the primary key from another related table, viz. Order. So according to the business rules it makes no sense for an Order to exist without the Customer but according to the database rules this is OK. An OrderLine cannot exist without the Order under either set of rules.


Your Answer

Interviews

Parent Categories