uniqueness of composite id with null component

Question

Hi, I am running into problems using unique constraints. The following combinations are allowed

A.name  B.name
foo     NULL
foo     bar
foo     bar1
foo1    bar

It should not be possible to create a new A with same name, only if it has a different B. With the constraints below it is possible to create

A.name B.name
foo    NULL
foo    NULL

Because NULL seems not to have effect on unique.

Any hints how to fix this?

class A {
  String name
  static belongsTo = [b:B]
  static constraints = {
    name(unique:'b')
    b(nullable:true)
  }
}

class B {
  String name
  static hasMany = [as:A]
  name(unique:true)
}

Answer 1

In the database structure, could you set the columns to NOT NULL DEFAULT 0 or similar, and then treat the zeros the same as you otherwise would the NULLs? Since the column is for names, there's likely to be no digits in the values anyway right?

Answer 2

I'm not entirely sure, but I think this will work:

name(unique:['b', 'name'])

Looking at the code for the unique constraint, it seems feasible. The constraint definitely lets you pass in a list of things to compare the uniqueness to. It calls this the uniquenessGroup. Then, when validating, it iterates over this list. Take a look starting at line 137 here: http://www.docjar.com/html/api/org/codehaus/groovy/grails/orm/hibernate/validation/UniqueConstraint.java.html

The code looks like this:

    if(shouldValidate) {
    Criteria criteria = session.createCriteria( constraintOwningClass )
        .add( Restrictions.eq( constraintPropertyName, propertyValue ) );
    if( uniquenessGroup != null ) {
        for( Iterator it = uniquenessGroup.iterator(); it.hasNext(); ) {
            String propertyName = (String) it.next();
            criteria.add(Restrictions.eq( propertyName,
                  GrailsClassUtils.getPropertyOrStaticPropertyOrFieldValue(target, propertyName)));
        }
    }
    return criteria.list();
}

So it depends on whether the GrailsClassUtils.getPropertyOrStaticPropertyOrFieldValue call will retrieve a property in the same class. Which based on the name it seems like it should.

I'm curious to know if it works for you.