Tuesday, October 12, 2010

Grails, GORM, Teradata and no primary key


I've stumbled upon a problem which is most probably a very common one though I couldn't find any clear answer right away... Mapping to so called natural keys with GORM.

Imagine you have a badly designed table without surrogate key acting as unique primary key. To map such a table in GORM you need to compose the ID of a mapped class using a composed key, that's obvious:
static mapping = {
id composite: [ 'reportId', 'managerId' ]

Since there's no restriction telling the database that the fields have to have values then if at least one of the fields contains null what you'll get in return is a null in a list where you'd expect your domain objects!!!

I have no idea if this is a bug but I surely believe that this is a design error in the database.

The fix for that (an ugly like hell one) is to add as much fields that always have values and remove those columns that can have nulls in them.

But I strongly recommend that you do add the surrogate, required (or better yet auto-populated) primary index.

Have fun!

No comments: