How to set uniqueness at DB level for a one-to-many association?

Question

My problem is simple but I could not find any GORM syntax for this.

Consider the following class:

class Article {
  String text

  static hasMany = [tags: String]

  static constraints= {
    tags(unique: true) //NOT WORKING
  }

}

I want to have one unique tag name per article defined in my constraints but I cannot make it with the above syntax. Clearly I need in DB schema something like:

create table article_tags (article_id bigint, tags_string varchar(255), unique (article_id , tags_string))

How can I do that?

PS: I am also stuck for setting constraints on tag minimum and maximum size

Answer 1

FYI, you can also use a custom validator in domain classes:

    static constraints = {
    tags(validator: { 
        def valid = tags == tags.unique()
        if (!valid) errors.rejectValue(
            "tags", "i18n.message.code", "default message")
        return valid
    })

At the database level, you can customize DDL generation by having the following code in grails-app/conf/hibernate/hibernate.cfg.xml:

<hibernate-mapping>
    <database-object>
        <create>
        ALTER TABLE article_tags
        ADD CONSTRAINT article_tags_unique_constraint 
        UNIQUE(article_id, tags_string);
    </create>
        <drop>
        ALTER TABLE article_tags 
        DROP CONSTRAINT article_tags_unique_constraint;
    </drop>
    </database-object>
</hibernate-mapping>

Answer 2

Initially I looked at the joinTable mapping to see if it would support a unique key, but it won't.

The best solution I can think of is the following combination:

• Manually run the SQL statement to add the unique constraint. If you have some sort of database management tool (e.g. Liquibase), that would be the ideal place.

• Explicitly declare the association as a Set. This should avoid Hibernate ever running into the unique constraint, anyway.

  class Article {
      static hasMany = [tags: String]
      Set<String> tags = new HashSet<String>()
  }
  

An alternate solution would be to explicitly declare your child domain (Tag) and set up a many-to-many relationship, adding the unique key to the join table there using constraints. But that's not really a great solution, either. Here's a primitive example:

class Article {
    static hasMany = [articleTags: ArticleTag]
}

class Tag {
    static hasMany = [articleTags: ArticleTag]
}

class ArticleTag {
    Article article
    Tag tag
    static constraints = {
        tag(unique: article)
    }
}

With this, though, you have to explicitly manage the many-to-many relationship in your code. It's a bit inconvenient, but it gives you full control over the relationship as a whole. You can find out the nitty gritty details here (the Membership class in the linked example is akin to the ArticleTag in mine).

Perhaps one of the gurus more familiar with GORM will chime in with a more elegant solution, but I can't find anything in the docs.