How do I create a composite primary key using GORM

Question

I have three domain classes: Beer, Review, and Reviewer.

I want the Review table to create a many to many relationship between Beer and Reviewer, so I want the primary key of Review to be a composite of the id fields from Beer and Reviewer. I'm following this Grails documentation.

http://grails.org/doc/latest/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.5.2.5%20Composite%20Primary%20Keys

Here are my domain classes.

class Beer {
    String name
    String type
    Brewery breweryId

    static hasMany = [ reviews : Review ]

    static constraints = {
    }
}

class Reviewer {
    String screenName

    static hasMany = [ reviews : Review ]

    static constraints = {
    }
}

class Review implements Serializable {
    int score
    Beer beer
    Reviewer reviewer


    static constraints = {
    }

    static mapping = {
        id composite:['beer', 'reviewer']
    }
}

I was getting compilation errors, but another answer here on stackoverflow said I needed to add implements Serializable. That took care of the error, but when I look in the database, I'm still not getting a composite primary key.

Here is what I'm seeing when I look at the table definition. I'm using Postgres.

       Table "public.review"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | bigint  | not null
 version     | bigint  | not null
 beer_id     | bigint  | not null
 reviewer_id | bigint  | not null
 score       | integer | not null
Indexes:
    "review_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fkc84ef75823f39326" FOREIGN KEY (beer_id) REFERENCES beer(id)
    "fkc84ef7587c483106" FOREIGN KEY (reviewer_id) REFERENCES reviewer(id)

I'd be happy with just a composite index with a unique constraint, but I can't figure out how to do that, either. I've been able to make a non-unique composite index, but this has two problems. One, it's non-unique. Two, the columns are specified in alphabetical order in the index (beer_id, reviewer_id). I'd like to specify the order of the columns in the index.

Answer 1

I have implemented a similar situation, with some different conditions:

1. There's no hasMany relationship.
2. Query to the join class is done by HQL
3. Using a more detailed mapping

When implementing like this, the mysql database is ok. (beer_id,reviewer_id) is the primary key.

class Review implements Serializable {

    Beer beer
    Reviewer reviewer

    static Review get(long beerId, long reviewerId) {
        find 'from Review where beer.id=:beerId and reviewer.id=:reviewerId',
            [beerId: beerId, reviewerId: reviewerId]
    }

    static boolean remove(Beer beer, Reviewer reviewer, boolean flush = false) {
        Review instance = Review.findByBeerAndReviewer(beer, reviewer)
        instance ? instance.delete(flush: flush) : false
    }
    ...

    static mapping = {
        table "REVIEW"
        id composite: ['beer', 'reviewer']
        beer(column: "beer_ID")
        reviewer(column: "reviewer_ID")
        version false
    }
}

I don't know what exactly causes your problem, but hope this gives you some hint about where the problem can be.

Answer 2

I took the Grails mandate that I shouldn't use Composite Primary keys as wise advice and am avoiding it. If so, I believe a viable alternative to solve your problem is the Composite Unique Constraint. ref: http://grails.org/doc/1.1.x/ref/Constraints/unique.html

Jacco's answer seems to be not correct, although it looks visually very close to correct, here is how you'd write a composite unique constraint for this problem:

static constraints = {
    beer(unique: 'reviewer')
}

whereas if the programmer wanted to link 3 db fields as unique, the correct formation is:

static constraints = {
    beer(unique: ['anotherField','reviewer'])
}

which looks like Jacco's answer, but the class name is not used as the first string of the constraint, the first field name is used.

I've just used this code structure in my own project app, and it seems to be behaving correctly, see also this on how to unit test unique constraints: http://www.ibm.com/developerworks/java/library/j-grails10209/index.html (see listing 11)

Answer 3

Try this, In your domain Review domain class:

static constraints = {
     review(unique: ['beer','reviewer'])
}

you might need to drop the table and let Gorm recreate it.

the above constraint means that a review must consist of a unique record of beer/reviewer combination. it is still many to many where a reviewer has multiple beers and visa versa but the reviews are unique.