Using a composite key in GORM

In section 5.5.2.5 of the grails document it says

GORM supports the concept of composite identifiers (identifiers composed from 2 or more properties). It is not an approach we recommend, but is available to you if you need it

Why isn't it a good idea? I have the fol开发者_StackOverflow社区lowing table definition:

User (Table)
   Column: userId (Primary Key)

FriendMap
   Composite Key Column: userId (foreign key from User) and friendId

Is this a bad idea?

---

It's not as bad of an idea for join tables used for many-to-many mappings, which is what it looks like you're using it for.

There are a number of arguments against using composite keys instead of using a single, numeric, incrementing id field. They mostly involve making it more complicated to change and refactor your domain.

For mapping classes, a good example to look at is Burt's mapping class between User and Role for the spring-security-core plugin.

---

I see evidence of why its not recommended. The friendMap class which has the composite key had also "version=false".

Under some cases, I was getting:

org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
        at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:85)
        at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:70)
        at org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:90)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:172)
        at org.codehaus.groovy.grails.orm.hibernate.events.PatchedDefaultFlushEventListener.performExecutions(PatchedDefaultFlush
EventListener.java:46)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)

I recently saw this when I was deleting a FriendMap object. This also doesn't happen all the time. I found comments online that this can be an issue with version=false and a composite key. I decided to back to a single, numeric, incrementing id field. Since then, I have not seen any issues. I dont know the entire issue but I wouldn't recommend a composite key with versioning turned off.

---

I completely disagree with the Grails guys on this one. Composite keys ensure consistency in your database and are necessary in many cases to have a properly normalized database.

See http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

This is an important basic database modelling concept, and I'm surprised it is glossed over in grails.