Nowadays, with the widespread use and deployment of Object-Relational Mapping (ORM) tools, you don't generally have to think too hard about such arcane issues as composite keys. Occasionally, you come across a situation where a composite key is required, and you need a strategy for this. This tip shows you how to implement composite keys with JPA and Hibernate.

"I think it's quite unusual to intentionally design a database this way nowadays" It is only unusual to design a database this way if you have a tool that makes it so hard to use tables with composite keys. It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this.

"I think it's quite unusual to intentionally design a database this way nowadays"

It is only unusual to design a database this way if you have a tool that makes it so hard to use tables with composite keys. It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this.

+1 In the phantom world where you think objects are the center of the universe and forget that the data is stored in a relational schema, that is ideal, but it's never optimal. That's why I get pretty pissed off hearing "map something to legacy database schema". It's not legacy, its good design. You should be able to architect an optimal relational schema and then map it to an optimal domain model and neither should effect the other much.

How can I agree more with you guys? That is why I am less and less convinced about the value of ORM in general and about the awfully lame JPA in particular. I will certainly sound outdated, shortsighted, and any other adjective can come up from your mind - reality is, ORM is introducing more aches than it manages to solve: lazy fetching, composite keys, stored procedures (!!!), only for keeping the code "readable". Please give me iBatis, a bunch of domain objects and the good ol' DAO. I will keep on living quite happily with them.

In the phantom world where you think objects are the center of the universe and forget that the data is stored in a relational schema, that is ideal, but it's never optimal. That's why I get pretty pissed off hearing "map something to legacy database schema". It's not legacy, its good design.

ORM is introducing more aches than it manages to solve

Yep. The Anti-ORM/Hibernate movement is already under way!

ORM is introducing more aches than it manages to solve

Yep. The Anti-ORM/Hibernate movement is already under way!

I have to agree with the sentiment that Hibernate burdens the developer more that it should. You end up having to design your database and object model to work well with hibernate. Ideally, tools should help you do what you want to do, not direct your approach.

ORM is introducing more aches than it manages to solve

Yep. The Anti-ORM/Hibernate movement is already under way!

I have to agree with the sentiment that Hibernate burdens the developer more that it should. You end up having to design your database and object model to work well with hibernate. Ideally, tools should help you do what you want to do, not direct your approach.

I agree with most of what you have said James, but from what I have seen using something like Hibernate actually makes using Composite keys easier. This becomes evident when queries are written.

I agree with most of what you have said James, but from what I have seen using something like Hibernate actually makes using Composite keys easier. This becomes evident when queries are written.

I was actually thinking about surrogate keys when I wrote that. I am working on something with Hibernate right now. It works fine. I get my work done. I just have to accommodate the tool in my design more that I think I should. I'm no expert on Hibernate and could definitely be doing it wrong but AFAICT, I would have to completely turn my object model upside down in order to have Hibernate insert parent keys first and thereby satisfy my non-null constraints on my foreign keys. I decided that I'd be better off just dropping the non-null constraints. There are other things like having to have a field on the object to hold the foreign key. Again, I'm no expert but it's a very invasive tool, at least for non-experts anyway. I guess I just wish tool makers would be more cognizant that their tool is not the most important aspect of the design of the application it is used in.

I get your point and I agree that it is not just a straight replacement for saving/updating when doing surrogate vs natural keys. For retrieving, still makes queries easier. On the other hand, doing it without an ORM would still be worse. Been there, done that. I have reverse engineered databases with NHibernate. It as easier to use than coding all the SQL. And I was able to switch from Access to SQL Server without a hitch. On the other hand, the guy who started the db and coded in SQL ... He had tons of problems. (FYI, on the new release, I'm not "letting" him code anything. :) )

I'm no expert on Hibernate and could definitely be doing it wrong but AFAICT, I would have to completely turn my object model upside down in order to have Hibernate insert parent keys first and thereby satisfy my non-null constraints on my foreign keys

I have not had that issue that i know of with surrogate keys. Hibernate handles it. Wonder what you are doing different. I'd love to talk to you about it. I've written quite a few projects with Hibernate and NHibernate. Anyway, with natural or surrogate keys, if you are using FK constraints, you have to insert the parent before the child.

On the other hand, doing it without an ORM would still be worse. Been there, done that. I have reverse engineered databases with NHibernate. It as easier to use than coding all the SQL. And I was able to switch from Access to SQL Server without a hitch. On the other hand, the guy who started the db and coded in SQL ... He had tons of problems. (FYI, on the new release, I'm not "letting" him code anything. :) )

<blockquote An invasive ORM can blur the seperation of concern between application and database development. Sure they both need to be designed in tandem, but how does one cleanly separate the two with schema specific metadata within your code? I believe a developer should not have change his entire domain model to conform to a database schema. From my past experience, we had a database development team concerned with maintaining and designing the database, and an application team concerned with, well, developing the application. The interface between the two worlds were simple stored procedures. The nice thing about this approach is that each team only had to focus on their discipline. My point/question is where do we draw the line here between modelling massive database in our application, or keeping the two specialisations separate. fit in with the database schema.

I believe a developer should not have change his entire domain model to conform to a database schema

Right. And the best way to insure that is to develop both. If you switch from surrogate to natural keys - you will have to change code.

From my past experience, we had a database development team concerned with maintaining and designing the database, and an application team concerned with, well, developing the application.

I am sure it works. But separating those will eventually (unless very minor) to larger issues. The application consists of all code. SQL is code. DLL is code. Do you have a team just for UI? Just for Services? Just for web services? Just for domain code? Just for ..?

simple stored procedures

Simple and SPs should not be in the same sentence.

The nice thing about this approach is that each team had to focus their discipline

So they did ALL the SQL? This means everytime users need a new query, you have to involved all players instead of 1.

My point/question is where do we draw the line here between modelling massive database in our application, or keeping the two specialisations separate. fit in with the database schema.

The database is not all that magical. Modern databases have become very good. And like I said, the application consists of all parts not just Java/C# code. Not sure what you mean by "modeling massive database in our application". Do you mean we should not use a Domain model? Or should not use annotations in them?

I get your point and I agree that it is not just a straight replacement for saving/updating when doing surrogate vs natural keys.

I think ORM is definitely worthwhile when you have to write to a database. For reads, I think it's overkill. I'm not arguing against ORM. I've used hibernate in the past and chose to use it in this case. I just find some things about it annoying and this foreign key thing just seems kind of stupid to me. It seems like it should be easily solved inside Hibernate.

I have not had that issue that i know of with surrogate keys. Hibernate handles it. Wonder what you are doing different. I'd love to talk to you about it. I've written quite a few projects with Hibernate and NHibernate.

Anyway, with natural or surrogate keys, if you are using FK constraints, you have to insert the parent before the child.

Start here: http://docs.jboss.org/hibernate/stable/core/reference/en/html/example-parentchild.html#example-parentchild-bidir And this explains the pain: http://blogs.warwick.ac.uk/colinyates/entry/hibernates_bizzare_interpretation

I just find some things about it annoying and this foreign key thing just seems kind of stupid to me. It seems like it should be easily solved inside Hibernate.

When I posted on of these links above, I noticed that I can set the child column to non-null in the mapping should solve my issue. I don't understand why this is "not the recommended solution", however. That the documentation suggests I use a different object structure is the kind of thing I am talking about.

For reads, I think it's overkill

Depends. Is your model complex? Then no. Do you want to work with domain objects instead of recordsets? Then no. It seems like extra work, but it ends up being less. I looked at your link. How many of those do you have? Add the methods like link says. Sure, it is an issue, but not a big one and I have seldom had it.

Depends. Is your model complex? Then no. Do you want to work with domain objects instead of recordsets? Then no. It seems like extra work, but it ends up being less.

I would say the more complex the data the more work it is to make it work with Hibernate. It just takes a lot longer to get things done if I have to create classes just to pull some information out of the database. I can build read only webservice extremely complex queries in an hour or so using SQL and be ready for QA.

I looked at your link. How many of those do you have? Add the methods like link says. Sure, it is an issue, but not a big one and I have seldom had it.

How many what? Parent-child relationships? Are you serious? From a data perspective? Thousands? Tens of thousands? I can't count them all. I've learned to avoid bi-directional relationships if I don't strictly need them. They create a lot more problems than they are worth. Anyway, you can say it's not a big deal but I think I've proven that you really have to adjust your programming to match hibernate if you want things to go smoothly. That's usually a bigger deal to me than database structure.

How many what? Parent-child relationships?

No, the link you showed which is many-to-may with. Single Parent Child is "easy". I was showing people who had zero ORM experience and no real OO development how to do it and they were picking it up pretty quickly ( They create a lot more problems than they are worth. Maybe. But the code you linked to is very much like code that was the "model" of how to do OO in VB many years ago.

I can build read only webservice extremely complex queries in an hour or so using SQL and be ready for QA.

Maybe you can. But you are the only person I know of who can. Joins that are complex in SQL will [mostly] be easier in HQL. I am sure we both can come up with examples of something easier in each. But still, sometimes pure SQL is the right thing to do. Doing webservices with objects is usually easier with domain objects, though.

Anyway, you can say it's not a big deal but I think I've proven that you really have to adjust your programming to match hibernate if you want things to go smoothly.

I don't see how you have proven it. If you are doing good OO dev you should be doing parent.addChild() not parent.getChildren().add().

That's usually a bigger deal to me than database structure.

I am not sure how the above code is more than the amount of code you have to create to deal with parent child relationships, or worse many to many, in pure data structures. Mind, you have have done this in COBOL, VB, Java, Powerbuilder ... . I guess the key are your words "to me". So if it is better for you, then you should do it that way. Doing something you don't believe in is not good no matter if it is better or not.

Joins that are complex in SQL will [mostly] be easier in HQL.

I find this too. The ORM know the links and uses them syntactically. The database knows the links too (XX references YYY foreign keys), but SQL does not use that knowledge. Database queries tools do have some room to improve.

Maybe. But the code you linked to is very much like code that was the "model" of how to do OO in VB many years ago.

I don't know what code you are referring to. I have a parent that references a bunch of children. It's been a while since I got my degree but I haven't seen anything to suggest that this is a bad approach. Child to parent relationships are basically useless except for use in bi-directional relationships. Where does your file system start? At the root or at the leaves?

I can build read only webservice extremely complex queries in an hour or so using SQL and be ready for QA.

Maybe you can. But you are the only person I know of who can. Joins that are complex in SQL will [mostly] be easier in HQL. I am sure we both can come up with examples of something easier in each. But still, sometimes pure SQL is the right thing to do. Doing webservices with objects is usually easier with domain objects, though.

It might just be work environments. My experience is that the ratio of people who (adequately) understand SQL to the number of people who (adequately) understand OO (in the Java sense) is about 10 to 1. For read-only services, Objects are usually just overhead. One exception would be if you need to implement some logic around the data. I often see people create objects just to hold data on it's way to become XML. They add no value. To me, that is a COBOL-style approach. These objects are just like data areas. If you have no business logic in your objects, they aren't really objects in the OOP sense. Not only that, but this approach usually entails having all these objects in memory at once requiring a larger memory footprint where SQL based approaches are much more conducive to streaming.

Anyway, you can say it's not a big deal but I think I've proven that you really have to adjust your programming to match hibernate if you want things to go smoothly.

I don't see how you have proven it. If you are doing good OO dev you should be doing parent.addChild() not parent.getChildren().add().

My point was that not all styles of development work well with Hibernate. If you want Hibernate to work well you must subject your code to it's constraints. You don't seem to be arguing that this isn't true but yet you say I haven't proven (proven is too strong a term, sorry) my point. Why?

That's usually a bigger deal to me than database structure.

I am not sure how the above code is more than the amount of code you have to create to deal with parent child relationships, or worse many to many, in pure data structures. Mind, you have have done this in COBOL, VB, Java, Powerbuilder ... .

I guess the key are your words "to me". So if it is better for you, then you should do it that way. Doing something you don't believe in is not good no matter if it is better or not.

I just don't see why I should have to add a association to the parent from my children when I don't need it for anything other than to make Hibernate happy. I really don't know what that has to do with COBOL. COBOL doesn't even have objects (well OO COBOL does but does anyone even use that?) Bi-directional associations in hierarchical relationships create GC issues. Holding a reference to any child of any node in the structure prevents any other node in the entire structure from being collected. Unless you really need to walk back up the tree, it's best to keep everything top-down.

... Simpleorm is both simple to use and simple to modify. It will allow Compisite primary keys, using compsite keys for foreign keys, and working on coherent detached datasets. Moreover it has a great metadata system that make it quite easy to automate UI generation (be it web ui, using BarracudaMVC for example, or gnome ui, or any other). Have a look at http://www.simpleorm.org/sorm/whitepaper.html

In the phantom world where you think objects are the center of the universe and forget that the data is stored in a relational schema, that is ideal, but it's never optimal.

By phantom do you mean heretical? Should we bring back burning at stake too?

That's why I get pretty pissed off hearing "map something to legacy database schema". It's not legacy, its good design.

[Checking calendar... Nope it is not 1980's ... hmmm]

You should be able to architect an optimal relational schema and then map it to an optimal domain model and neither should effect the other much.

Yup. That is what ORM gives you. Actually, ORM will let you have an non-optimal one too. Sadly, your design with composite keys is not optimal nor good design, with or without Objects being central to the design.

You should be able to architect an optimal relational schema and then map it to an optimal domain model and neither should effect the other much.

That is one way to do it. Another idea would be that you create only one model of your domain, not two, and zero mappings.

Yup. That is what ORM gives you. Actually, ORM will let you have an non-optimal one too. Sadly, your design with composite keys is not optimal nor good design, with or without Objects being central to the design.

My optimal object design does not include synthetic id fields. Most of the demands of ORM can be accommodated without to much pain if that is your goal. But the result is neither an optimal database or object model.

It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this.

??? Use of surrogate keys as primary keys is one of the best design patterns for databases: http://en.wikipedia.org/wiki/Surrogate_key#References

AFAIK from DB design practices field, composite keys are suitable when: - queries on child tables mostly don’t need ful info from parent table (joins are avoided then) - key values hardly going to changed in foreseen future (update of dependent children code and structure is costly) - semantic rules dictate: child record is meaningful only for this composite key combination - no multilevel composite key transfer is expected (happens when this key becomes part of child PK and get used in subsequent children)

"I think it's quite unusual to intentionally design a database this way nowadays"

It is only unusual to design a database this way if you have a tool that makes it so hard to use tables with composite keys. It is always better to design a table to use natural primary keys than artificial keys and it is terrible that the tool makes it so difficult to support this.

Completely disagree. Using logical keys is a really shortsighted idea. I learned this the hard way. I worked on a system where the logical key of a top level table of a large database structure needed to have columns added (then later column removed.) Because pretty much every table in the system had a reference to this logical key, it meant every table had to be modified and every piece of code that touched a table had to be modified and retested. In other words, pretty much the entire database and many thousands of lines of code had to be fixed and retested requiring many thousands of man-hours of work. If the keys had been unrelated to the logical key, a lot of that work would not have been necessary. Ultimately, using logical keys as primary keys is painting yourself into a corner without significant benefit.

That depends whether you pick a "good" natural key. If you pick one that you know will weather the time, than you'll be fine. Of course, if you pick one that only represents the business rules today and might change tomorrow, then you're better off with a surrogate key. With that said, even surrogate keys change. Take for example an app using an integer based sequence and then realizing that they need to partition and distribute the data and now they want to go with a UUID surrogate key approach. Nothing will shield you from the one off issues where keys change, outside of planning for it from the beginning and ensuring you reduce the chances of it happening.

That depends whether you pick a "good" natural key. If you pick one that you know will weather the time, than you'll be fine. Of course, if you pick one that only represents the business rules today and might change tomorrow, then you're better off with a surrogate key.

Since I can't predict the future and the cost of using surrogate keys is minuscule, I can't see any rational reason to adopt a logical key approach. I wish I had a dollar for every time I was told that something would never change.

With that said, even surrogate keys change. Take for example an app using an integer based sequence and then realizing that they need to partition and distribute the data and now they want to go with a UUID surrogate key approach. Nothing will shield you from the one off issues where keys change, outside of planning for it from the beginning and ensuring you reduce the chances of it happening.

As I understand it, UUIDs depend on the unlikeliness of very large numbers colliding. If you are afraid of UUIDs colliding with your existing ids, should you really be using them? Also, what prevents you from using a UUID generator and then offsetting it past the sequentially generated keys? All of that aside, It doesn't matter at all if my surrogate keys change. I wasn't talking about changing the values of the keys (although that can be a problem when logical keys are used) I was talking about the number of columns used to define the logical key. You only ever need one column to define a surrogate key.

I wish I had a dollar for every time I was told that something would never change.

Forgot the end: "...and it changed."

I'm pretty much in the surrogate key camp. However, I believe the ISO codes for countries and currencies are good candidates for primary keys (and I wouldn't describe them as surrogate). e.g. "USD", "EUR" etc ... ISO-4217 In my book, good natural keys are pretty rare though - you want to be really really really sure it does not change (aka just reiterating what James said). I also think composite keys are generally fine for "intersection tables".

I'm pretty much in the surrogate key camp. However, I believe the ISO codes for countries and currencies are good candidates for primary keys (and I wouldn't describe them as surrogate).

e.g. "USD", "EUR" etc ... ISO-4217

In my book, good natural keys are pretty rare though - you want to be really really really sure it does not change (aka just reiterating what James said).

I also think composite keys are generally fine for "intersection tables".

Not only are natural keys rare, they are typically strings ... and joining on strings ...

I'm pretty much in the surrogate key camp. However, I believe the ISO codes for countries and currencies are good candidates for primary keys (and I wouldn't describe them as surrogate).

e.g. "USD", "EUR" etc ... ISO-4217

In my book, good natural keys are pretty rare though - you want to be really really really sure it does not change (aka just reiterating what James said).

I also think composite keys are generally fine for "intersection tables".

Surrogate Keys is not a concept created by the Hibernate folks to make it easier for their framework. It is something that DBAs, data designers etc. came up with many years ago, well before Java even appeared. It was designed to reduce the huge amounts of work required if columns from one table that were used as foreign keys ever changed. And it was designed to make databases easier to maintain. I'm definitely in the surrogate key camp as well. There are a few realistic natural keys, like currency codes and even then you havee to be careful. If you've ever worked on a system where something that seemed absolutely set in stone, such as account numbers, national insurance numbers etc. and then the business decided they needed to change the format, you'll know how much a nightmare it can be with natural keys and how easy it is with surrogate keys. The natural key in the example is unbelievably bad but I would guess that that's the whole point of showing how to do composite keys in Hibernate, sometimes you havee to deal with badly designed databases and Hibernate can cope with them.

I wish I had a dollar for every time I was told that something would never change.

Forgot the end: "...and it changed."

Hibernate is a ORM framework, and as any kind of good framework it has a philosophy (surrogated keys). You can't take any piece of code and make it work on Spring without any change. Or in any other framework.

I wish I had a dollar for every time I was told that something would never change.

Forgot the end: "...and it changed."

Hibernate is a ORM framework, and as any kind of good framework it has a philosophy (surrogated keys).

You can't take any piece of code and make it work on Spring without any change. Or in any other framework.

I don't disagree but that has no bearing on what I was discussing.

The link went to the article that had: create table PURCHASE_ORDERS ( ... primary key (street, city) ) Street and City as a primary key for purchase_orders - holy #$%^ batman!!!! I'm struggling to describe how bad I think this example is. Wow... stunned...

The link went to the article that had:

create table PURCHASE_ORDERS (
...
primary key (street, city)
)

Street and City as a primary key for purchase_orders - holy #$%^ batman!!!! I'm struggling to describe how bad I think this example is. Wow... stunned...

Totally agree. I had posted on this on Dzone yesterday. A horribly crappy example.

The link went to the article that had:

create table PURCHASE_ORDERS (
...
primary key (street, city)
)

Street and City as a primary key for purchase_orders - holy #$%^ batman!!!! I'm struggling to describe how bad I think this example is. Wow... stunned...

Agreed. Even though most intermediate to advanced developers recognize this as poor design, a lot of beginners read these articles and see IBM splashed across the top and think, well, it's from IBM so it must be good design!!

I would remember to myself first that ORM != Hibernate. Some of the painful things you have to suffer using a tool are not necessarily intrinsic properties of a technology. And if you think that using an ORM tool is a shortcut to manage tables and columns then you deserve Pro*C, SQLJ, LINQ and any other hell stuff. Guido

This is a very bad example. The name of that post should be "How not to implement composite keys with JPA". Leaving out the poor choice of a PK that has already been mentioned it misses the most important issue: Overriding the equals() method. I suggest reading https://www.hibernate.org/109.html

The most terrible wheel ORM invented is the query language. There are JDOQL, HQL, JPQL ... most of them has very short lifespan, duplicating each other but not compatible...

The most terrible wheel ORM invented is the query language.

Really? How much real OO development have you done with an ORM such as Subclasses mapped to multiple tables?

The most terrible wheel ORM invented is the query language.

You mean you should use SQL for everything ? even when the datastore is not RDBMS ? Oh dear, I think we've been through this subject before. The application is using OO, so it makes a lot of sense to use a query language that targets such a domain, whilst also allowing use of SQL in specific areas where the datastore suits it. --Andy (DataNucleus)

The most terrible wheel ORM invented is the query language.

You mean you should use SQL for everything ? even when the datastore is not RDBMS ? Oh dear, I think we've been through this subject before. The application is using OO, so it makes a lot of sense to use a query language that targets such a domain, whilst also allowing use of SQL in specific areas where the datastore suits it.

--Andy (DataNucleus)

I can see many ORM-QLs will be ruled out & obsoleted from the market soon. If your application is using such an ORM tool, it will be costly to maintain & extend. For example, it will be difficult to find a staff to maintain a JDO 1.0 application 2 years later. However, an application using proper SQL framework such as ibatis or spring-jdbc would be pretty easy to maintain & extend even for people come from other technical background such as c#, vc. Some orm tools supports non-relationsl datastores via special dialect (for example, jdbc driver for excel, csv), this is interesting.

I can see many ORM-QLs will be ruled out & obsoleted from the market soon. If your application is using such an ORM tool, it will be costly to maintain & extend. For example, it will be difficult to find a staff to maintain a JDO 1.0 application 2 years later.

You were predicting the end of the world some 3+ years ago sir. It didn't happen. You dived into threads back 3+ yrs ago on unrelated topics, and you do the same here. This thread is about composite keys in JPA. This has very little to do with query languages on ORM and non-ORM.

However, an application using proper SQL framework such as ibatis or spring-jdbc would be pretty easy to maintain & extend even for people come from other technical background such as c#, vc.

Some orm tools supports non-relationsl datastores via special dialect (for example, jdbc driver for excel, csv), this is interesting.

Using JDBC to persist OO information is not interesting; not even slightly. The API you propose is not OO nor attempts to be so. It is good for what it was intended for, as is SQL. JDBC and SQL aren't some sort of global solution to world hunger. For this very reason iBatis and the like target a specific audience. You sir, do not. Use of SQL on OODBMS is a minority interest activity, for the very reasons already outlined. You haven't made any attempt to present a reasoned coherent argument as to why you think SQL is a good idea on non-RDBMS datastores. This was also the case some 3+ years ago. I'll leave you to have a good think :-)