Takeaway: In this article, I recommend that you use composite primary keys for child tables in composite relationships. If a child record is part of its parent record, then the parent’s primary key should be part of its child’s primary key.
That means that this:
create table CD ( CDId int identity, Name nvarchar(max), Artist nvarchar(max), Genre nvarchar(max), primary key (CDId) ) create table Track ( CDId int references CD(CDId), TrackId int, Name nvarchar(max), primary key (CDId, TrackId) ) |
… is better than this:
create table CD ( CDId int identity, Name nvarchar(max), Artist nvarchar(max), Genre nvarchar(max), primary key (CDId) ) create table Track ( TrackId int identity, CDId int references CD(CDId), TrackNumber int, Name nvarchar(max), primary key (TrackId) ) |
First some definitions, and then I’ll briefly describe the benefits of this practice.
Composite Relationship
I’m using the UML definitions when I talk about these relationships. Composite relationships between two tables occur when a child points to a parent and when the child is considered part of the parent. A quick test for this relationship is to ask whether records are deleted when the parent is. Or you can ask whether an object gets copied when the parent does. Track-CD is a good example of an composite relationship. Other examples might be Department-Company or SalesOrderDetail-SalesOrderHeader .
Relationships That Are Not Composite
For contrast, some relationships that are not composite include aggregation, association or dependency relationships. For example, the driver-vehicle relationship is not an composite relationship. It’s a “has a” relationship. Both vehicles and drivers have their own life cycle and it’s common for vehicles to change owners.
Benefits
Since the child records live and die (and get copied) with the parent record, it makes sense to have the parent PK column be the first column of a child’s primary key. The values of the child’s primary key almost never change. Some other benefits:
- Index lookups and index scans become more efficient.
- When you extend this practice to children of children, it’s even better. You have the opportunity for less joins on many queries.
- The SQL you have to write turns out so much cleaner. I hinted about that here: My Favourite Data Modelling Example.
- In some cases business rules can be enforced that couldn’t before.
- Delete statements and purge strategies become simpler.
Is This Obvious?
Maybe when you read the Track-CD example above, it was obvious to you that the first example is better. But I see the second example way too often. So often that I felt it deserved a blog post.
If you want, you can look for this stuff in your own tables to double check. Try this out a couple times:
- Find a foreign key somewhere in your database.
- Is the relationship enforced by the foreign key an composite relationship? In other words, is one record part of the record it refers to? (e.g. Tracks are a part of their CDs)
- Then are the foreign key columns part of the primary key? I hope so.
