CODEWITHSUNDEEP
hibernategrailsgrails-orm
Vahid Pazirandeh
Vahid Pazirandeh

Reputation: 1592

Grails 2.3 and Hibernate4: unique constraint name collision

UPDATE: This may turn out to be a Grails bug: https://jira.grails.org/browse/GRAILS-11600

Ever since upgrading my Grails app to Hibernate4 I am getting errors related to creating many of my unique constraints on startup, using both H2 and Postgres. For example:

ERROR hbm2ddl.SchemaExport  - HHH000389: Unsuccessful: alter table bravo add constraint unique_name  unique (bravo_prop, name)
ERROR hbm2ddl.SchemaExport  - ERROR: relation "unique_name" already exists

I looked into the problem by creating a new simple Grails app. I've discovered that when using Hibernate4 the name of the constraint that gets created in the database is not "globally unique". Let me explain.

If I have a property called "foo" with a unique constraint then the constraint's name in the database may end up being called "unique_foo" with Hibernate4 whereas with Hibernate3 it's called more like "[tablename]_foo_key".

When does this become a problem? It's an issue when I have multiple entities that have unique constraints on properties of the same name. For example: Entity1 has prop "foo" with a unique constraint, and so does Entity2. SchemaExport will fail when creating the unique constraint for Entity2.

Here's some code to illustrate:

class Alpha {
    String name
    String alphaProp
    String alphaProp2

    static constraints = {
        // With Hibernate4.3.5.1|4 and either Grails 2.3.8 or 2.3.11, produces static constraint name: 'unique_name' => BAD
        // With Hibernate3, produces a unique constraint name: 'alpha_alpha_prop_name_key' (notice the entity namespace prefix) => GOOD
        name unique: 'alphaProp'

        // With Hibernate4.3.5.4 and Grails 2.3.11, produces static constraint name: 'unique_name' => BAD
        // With Hibernate3 and Grails 2.3.11 produces unique name: 'alpha_alpha_prop2_alpha_prop_name_key' => GOOD
//        name unique: ['alphaProp', 'alphaProp2']

        // With Hibernate4.3.5.4 and Grails 2.3.11, produces random constraint name such as: 'uk_5h01nufunln3bv2bgxtnr9sxw' => GOOD
        // With Hibernate3 and Grails 2.3.11 produces unique name: 'alpha_name_key' => GOOD
//        name unique: true
    }
}


class Bravo {
    String name
    String bravoProp

    static constraints = {
        // This constraint creation will fail if a constraint by this name was already created by Alpha
        name unique: 'bravoProp'
    }
}

Help? :)

Upvotes: 1

Views: 458

Answers (2)

Joe Jadamec
Joe Jadamec

Reputation: 156

An easier way to solve this problem if you don't mind adding an unused attribute to your domain...

  1. Add a nullable Boolean attribute to each domain with this problem, name the attribute like ucdomainName, and default its value to null. Example:
Boolean ucBravo = null
  1. add a unique constraint using the new attribute name, and make it nullable. Example:
ucBravo nullable: true
ucBravo(unique: ['name','bravoProp'])

There is no need to populate or findBy the new 'ucBravo' attribute, because it is defaulted to null. But using it as the first attribute in the domain constraint will cause the Database constraint name to be domain specific - like 'unique_uc_bravo' instead of 'unique_name'.

Example of Bravo domain with these changes:

class Bravo {
    String name
    String bravoProp
    Boolean ucBravo = null

    static constraints = {
        // creates a Database constraint named like 'unique_uc_bravo'
        ucBravo nullable: true
        ucBravo (unique: ['name','bravoProp'])
    }
}

Upvotes: 1

Vahid Pazirandeh
Vahid Pazirandeh

Reputation: 1592

I never heard back from the Grails developers so I will consider this question answered as a bug: https://jira.grails.org/browse/GRAILS-11600

See that JIRA issue for a workaround that I applied, if you are up for forking the grails-data-mapping packages. Not pretty but got the job done.

Upvotes: 0

Related Questions