multi-column unique constraint is ignored

[donalmurtagh]

I've defined 2 domain classes that are mapped using the "no collections" approach described here

class IncomeScenario {

    String title

    Collection<IncomeDelta> getItems() {
        IncomeDelta.findAllByIncomeScenario(this)
    }

    def beforeDelete() {
        withNewSession {
            executeUpdate('delete IncomeDelta where incomeScenario = ?', [this])
        }
    }
}

class IncomeDelta {

    IncomeScenario incomeScenario
    String name

    static constraints = {
        // can't have multiple income scenario items with the same name
        name unique: 'incomeScenario'
    }
}

However, it seems this constraint is ignored because I'm not prevented from saving an IncomeScenario that has multiple items with the same name, e.g.

def scenario = new IncomeScenario(title: 'foo').save()
new IncomeDelta(name: 'name', incomeScenario: scenario).save()

 // this one should fail, but it doesn't
new IncomeDelta(name: 'name', incomeScenario: scenario).save(failOnError: true)

[graemerocher]

Which version of Grails?

[donalmurtagh]

Which version of Grails?

2.5.0

[donalmurtagh]

I've investigated this a bit further, and although the constraint is applied at the database level (via a unique index), it's not enforced by GORM validation, e.g.

def scenario = new IncomeScenario(title: 'foo').save()
def deltas = [
    new IncomeDelta(name: 'name', incomeScenario: scenario)
    new IncomeDelta(name: 'name', incomeScenario: scenario)
]

// the below evaluates to true, but it should be false
def allDeltasValid = deltas.every { it.validate() }

[graemerocher]

your example above is invalid, since neither of the two items in the deltas list have been saved

[davydotcom]

Just a sidenote on this. rails active_record would say this was invalid as it does the validation within active_record not via a db constraint. Doing this also provides some added benefits such as defining a uniqueness constraint that allows blank values not to be included in the criteria or allows a conditional closure (Proc) to not restrict it (say soft deleted items).

[graemerocher]

Grails' validation also does not rely on a db constraint, so I don't think there is any different here

[donalmurtagh]

your example above is invalid, since neither of the two items in the deltas list have been saved

@graemerocher My example was intended to illustrate the fact that GORM validation will pass even though saving the objects will fail (because of the DB constraint). This can be demonstrated explicitly by changing the example to the below

def scenario = new IncomeScenario(title: 'foo').save()
def deltas = [
    new IncomeDelta(name: 'name', incomeScenario: scenario)
    new IncomeDelta(name: 'name', incomeScenario: scenario)
]

// all IncomeDelta instances pass validation
assert deltas.every { it.validate() }

// so saving them should succeed, but it doesn't because of the DB constraint
deltas*.save(failOnError: true)

My understanding is that to the greatest extent possible, the result of validate() should indicate whether saving will succeed or fail. There are some edge cases involving unique constraints wherein validation will pass, but saving will fail, but this is not one of those cases.

[graemerocher]

Please upload an example to Github that reproduce this issue

[donalmurtagh]

I've made a sample app available here. Click the link on the homepage to demonstrate the bug

[ejaz-ahmed]

I too have faced same issue with Grails 2.3.11 and was able to produce on latest version of Grails as well. My design was based on Burt Beckwith's talk on Spring Security, so I took his [https://github.com/burtbeckwith/hacking_madrid](repo hacking_madrid) and was able to reproduce it with grails version (2.5.0). There is a domain class Org_User.groovy which has following constraint

static constraints = {
        organization unique: 'user'
    }

Even with this constraint, I can have non-unique users within an organization. I had to remove unique constraint from Users domain class to test this.

[davydotcom]

This seems to be ignored in a grails 3.1.0.M2 GORM constraint unit test as well

[bassmartin]

For the 'validate()' to fail in a unit test, the first entity must be saved and flushed, then the 'validate()' of the second entity will fail.

[ejaz-ahmed]

This seems to be not working with latest grails testing library which is based on traits. It only happens for tests.

[jeffscottbrown]

This seems to be not working with latest grails testing library which is based on traits. It only happens for tests.

Does it happen with and without having invoked mockDomain ... for the domain in question?

[benrhine]

I actually just bumped into this again with Grails 3.2.13 using Gorm 6.1.9 where the object has a constraint

static constraints = {
      name unique: 'modifiedBy', nullable: false
}

If the constraint is modified to unique: true the unit will pass but not otherwise.

Integration testing (with the exact same test) will pass with no issue.

[zyro23]

any update on this one? what i am currently seeing with a unique constraint like this (grails-3.3.9/gorm-6.1.11):

myOneToOneAssociatedEntity unique: ["mySimpleProperty1", "mySimpleProperty2"]

• from the various unique constraints this one is applied
  • https://github.com/grails/grails-data-mapping/blob/v6.1.11/grails-datastore-gorm/src/main/groovy/org/grails/datastore/gorm/validation/constraints/builtin/UniqueConstraint.groovy
• UniqueConstraint.processValidate triggers an auto-flush
  • https://github.com/grails/grails-data-mapping/blob/v6.1.11/grails-datastore-gorm/src/main/groovy/org/grails/datastore/gorm/validation/constraints/builtin/UniqueConstraint.groovy#L132
• then, the validator is called again during the flush but this time it does not actually validate because it regards the entity as not-changed/not-dirty
  • https://github.com/grails/grails-data-mapping/blob/v6.1.11/grails-datastore-gorm/src/main/groovy/org/grails/datastore/gorm/validation/constraints/builtin/UniqueConstraint.groovy#L84-L92
• so the query is flushed to the db and a corresponding org.hibernate.exception.ConstraintViolationException is thrown instead of the validator handling it