Grails GORM unique constraint

Question

I've got the following ListProducts domain object;

String name
Date lastUpdated
Date dateCreated = new Date()
String lastUpdatedBy
CatLists catLists // 
Product product // has a composite primary key
ListProducts listProducts

static constraints = {
    name nullable: true, blank: true
    catLists nullable: false
    lastUpdatedBy nullable: true
    lastUpdated nullable: true
    dateCreated nullable: false
    product unique: 'listProducts'
}

static mapping = {
    version true
    table 'list_products'
    sort "name"
}

The Product domain class has the following constraint which is a composite key

FOREIGN KEY (product_fulfillment_sku, product_merchant_id) REFERENCES product(fulfillment_sku, merchant_id)

as it doest not have a primary key i.e. ID as the table is dropped due to a batch job so the unique values are the sku and the merchant id.

I can see in the list_products table the product_fulfillment_sku, product_merchant_id and cat_lists_id columns, but how do I represent this in grails domain classes so that product cant be added if it already exists?

What I want to achieve is that a product can be in many ListProducts groupings but only once per grouping.

Grails v2.2.1 against Postgres Sql database!

Any ideas?

J

So this domain object that you show here represents a grouping? — Luis Muñiz
Correct. So i refactored the code so that the catLists domain object adds and removes items from its list. If somebody deletes the list then all the references from the ListProducts table is also removed. I took some inspiration from this blog mrpaulwoods.wordpress.com/2011/02/07/… — user1859465

user1859465 user1859465 · Accepted Answer · 2014-03-03T16:59:33

So the work around I got was to remove the dependency on the unique constraint. This would also block parrallel lookups as the table is huge. Took some inspiration from Performance Collections

Grails GORM unique constraint

2 Answers