grails composite "unique constraint", but how?

Question

I'm very close to a solution but anything is still wrong, hope to get help, thanks in advance.

I have a Customer domain model like:

class BoCustomer implements Serializable{

    String firstName
    String lastName
    String emailID
    Company company
}

So, I have a primary key = "id" that's okay. So further I need kind of unique constraint that "checks" the following: "only one unique email address for one company" so that inserting the same e-mail should be allowed but only for different companies. Inserting ([email protected], company-id: 1) and inserting ([email protected], company-id: 1) is not allowed, but inserting ([email protected], company-id: 1) and inserting ([email protected], company-id: 2) is allowed.

So I tried so far:

static mapping = {
    id column: "customer_id", generator: "identity"
    emailcompany composite: ['emailID', 'company'], unique: true
}

(seems to be "good", but not exactly what I want)

BUT i don't want another column, in my try called "emailcompany" - but I need something like a unique constraint.

Bari

Answer 1

You can specify this behaviour by one of the main constraints available: unique

class BoCustomer implements Serializable{

    String firstName
    String lastName
    String emailID
    Company company

    static constraints = {
        emailID(unique: 'company')
            // OR
        company(unique: 'emailID')
    }
}

You can see the full spec of the unique constraint here http://grails.org/doc/latest/ref/Constraints/unique.html

Priam

Answer 2

If you want to enforce this with a constraint, you should be able to use a custom validator so that it returns false if a BoCustmer already exists with the same emailID and company. It's been a while since I've used it, but I think something like this should work:

class BoCustomer implements Serializable{
    String firstName
    String lastName
    String emailID
    Company company

    static constraints = {
        emailID( 
            validator: { val, obj ->
                def customerWithSameEmailAndCompany = BoCustomer.findByEmailIDAndCompany(obj.emailID, obj.company)
                return !customerWithSameEmailAndCompany
            }
        )
    }
}

Travistravus

Hey Kaleb, thanks for your answer, but I'm sorry. I tried your code but it's still possible to add the same e-mail with the same company id twice (and more) :-( – Bari

Do you run a .validate() on the BoCustomer instance before calling .save()? And if so, the validate's returning true? – Travistravus

Yes I run a .validate() before calling .save(). And it's returning "true" yes, i checked it with debugger breakpoint and a test variable - it's returning true unfortunately :-\ – Bari

The code does work (I just tried it). You have to call save with flush:true to get the rows persisted before the validation will fail. If you're inserting two rows in which one should be rejected before a flush or the transaction ends, the validator won't catch it. Also save calls validate inherently, so it's not needed to call it on it's own unless you need to validate prior to the save (or you can use failOnErrors:true with the save to catch any errors when the save is attempted). – Candor