Set unique constraint against combination of values

Question

I have two tables. One contains a list of countries (ISO code and full name), the second one contains status of reg. Each reg entry can only have one country, but each country can have multiple reg entries. So far quite straight forward.

The regstat class is set up as follows:

class Regstat {
   static hasOne = [country: Country]
   String reg
   int status
   Date impdate
   static constraints = {
      reg(inList: ["FATCA", "ITC2014", "AEOI"])
   }
   static mapping = {
      index: 'reg'
   }
}

Here is the problem? I want to add a constraint which states that I only want a single entry of reg per country. So for example, a country can have a FATCA, IRC2014 or AEOI, but only one of each. Any suggestions how I would create such a constraint? Something along the lines of

constrain = {
   reg+country(unique)
}

Thank you Opal for correcting the grammar as well:), and it is a grails question as well — vrghost, Sep 24 '14 at 10:18
possible duplicate of [Grails domain class: unique constraint for multiple columns](http://stackoverflow.com/questions/7583380/grails-domain-class-unique-constraint-for-multiple-columns) — Joshua Moore, Sep 24 '14 at 10:33

score 2 · Accepted Answer · answered Sep 24 '14 at 10:25

The unique constraint can take another property name (or list of names) to act as the "scope" of the uniqueness:

static constraints = {
  reg(inList: ["FATCA", "ITC2014", "AEOI"], unique:'country')
}

You get the same constraining behaviour whether you set reg to be unique with respect to country or country to be unique with respect to reg, the difference is which of the two properties is considered "invalid" when the constraint fails - reg unique:'country' would put the error on reg whereas country unique:'reg' would put the error on country.

Sorry that it took a while, but got it tried and it worked a charm — vrghost, Sep 28 '14 at 21:59

Set unique constraint against combination of values

1 Answers1