Spotlight on... Composite Keys
For those of us who have taken a course on database design in college or university, you may have run across a concept called 'Composite Primary Keys' (or sometimes 'Compound Keys' or 'Concatenated Keys', and abbreviated CPKs). It's usually right before you tackle JOINs and right after you fight with the "surrogate key" or "primary key" concept.
Boiling CPKs down, they're just a way of identifying a row by multiple keys
rather than one. So instead of an auto_incrementing "serial" primary key (as in
id), you'd have a combination of
would uniquely identify a row.
CPKs aren't as prevalent in the Rails world as Serial Keys (such as the
:id column), but if you're going to support legacy,
integration or reporting databases or just de-normalized schemas for performance
reasons, they can be invaluable. So sure, Surrogate Keys are a great
convenience, but sometimes they just aren't an option.
Let's briefly take a look at how a few ruby ORMs support Composite Primary Keys and then we'll talk about DataMapper's support for CPKs.
Unlike ActiveRecord, Sequel supports CPKs natively:
1 class Post < Sequel::Model 2 set_primary_key [ :category, :title ] 3 end 4 5 post = Post.get('ruby', 'hello world') 6 post.key # => [ 'ruby', 'hello world' ]
p(attribution). example compiled from http://github.com/jeremyevans/sequel.
The latest DataMapper was designed from the ground up to support CPKs:
1 class Pig 2 include DataMapper::Resource 3 4 property :id, Integer, :key => true 5 property :slug, String, :key => true 6 property :name, String 7 end 8 9 pig = Pig.get(1, 'Porky') 10 11 pig.key # => [ 1, 'Wilbur' ]
We declared our keys by adding the
:key => true to the appropriate properties.
The order is important as it will determine the order keys are addressed
throughout the system.
Next, we mixed and matched the keys' types.
:id is a Integer, but
:slug is a
String. DataMapper didn't flinch when we defined a key column as a String
because it supports Natural Keys as
Lastly, when retrieving rows via
 with a CPK, we supplied the keys
in the order they were defined within our model. For example, we defined
:slug second; later, we retrieved Porky by specifying his
:slug in the same order. Additionally, when we asked Wilbur for his keys,
he handed us an array in the order the keys were defined.
We didn't need to mix in an external library to get support for CPKs, nor did we
need to call a
set_primary_key method and then supply more than one key to it.
DataMapper supports Composite Primary Keys intuitively and without compromise!
In later "Spotlight On..." articles, we'll examine and demonstrate other DataMapper features or persistence concepts as well as compare similar features with other ORMs or libraries.
*[CPK]: Composite Primary Keys
Contribute a “Spotlight On…” Article
Got something important to say? Want something explained a little better or demonstrated? Contribute or request a “Spotlight On…” article! Email the DataMapper Mailing List with the request or contribution and we’ll post it here.