Composite keys: threat or menace?

First, some terminology: composite keys are (primary) keys in a database that are made up of multiple columns. For example, if you have a Person table with primary key personId, and a Place table with primary key placeId, then you may end up with PersonPlace table keyed by personId and placeId.

I’ve been vaguely aware that Hibernate discourages the use of composite keys and that they’ve fallen out of favour among a number of programmers. But I’ve never really paid attention to the reasoning behind this distaste – in fact, it’s never really made a difference to me and I’ve usually just happily worked with whatever data model I’ve been given. And, to be honest, there are cases in the past where I’ve willingly designed databases with composite keys.

I know better now. Composite keys are a tool of the devil.

Here’s the problem, or at least one of them: object/relational mapping (ORM) does not deal well with composite primary keys. When we’re dealing with single-column keys, it’s easy to see how the key maps to a particular object. It’s not as easy with multi-part keys and mapping between the database and the object hierarchy became more troublesome.

All it takes is one generated key column to make developers happy. That’s not too hard, is it? By all means, add restrictions to the foreign key columns so that there’s a unique constraint on a particular patient/place combination, but really, would it kill you to add a personPlaceId?

Adding to my particular pain is that different persistence mechanisms solve the mapping problems in different ways. So, purely hypothetically, if you were trying to convert between EJB mapping and Hibernate mapping, it’s the composite key relations that cause you a lot of pain and lead to a drinking problem. A hypothetical drinking problem, of course.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply