Creating domain classes with foreign key constraints in groovy

Question

Hi I am trying to create a domain class with foreign key constraint to another existing domain class New domain class is ArEntitlement defined as below

package ars
import gra.Resources
class ArEntitlement {
    long id
    String entitlementname
    String entitlementdesc
    String entitlementcomm
    Integer departmentid
    Resources resource
    Integer version

    static belongsto =[resource : Resources]

    static mapping={
    table 'ar_entitlement'
    id(generator:'increment')
    column{
        id(column:'id')
        }
    }
}

Resource domain class is defined as follows (it was created before)

package gra
import com.gra.transaction.UserTransaction

class Resources {
    Long id=1
    String resourceName
    Double resourceType
    String resourceOwner
    Double riskScore
    Integer decommissioned
    String resourceClass
    Long resourceSegment
    String targetIp
    String resCriticality
    Long resourceGroup
    Integer disabled

    static hasMany=[userTransactions:UserTransaction]

    static mapping = {
        table 'resource'
        version false
        id(generator:'increment')
                column{
                        id(column:'id') }

    }
    static constraints=
    {
        resourceName(nullable:true,blank:false)
        resourceType(nullable:true,blank:false)
        resourceOwner(nullable:true,blank:false)
        riskScore(nullable:false,blank:false)
        decommissioned(nullable:false,blank:false)
        resourceClass(nullable:false,blank:false)
        resourceSegment(nullable:false,blank:false)
        targetIp(nullable:false,blank:false)
        resCriticality(nullable:true,blank:false)
        resourceGroup(nullable:false,blank:false)
        disabled(nullable:false,blank:false)
    }
}

The resulting tables created dont have a foreign key mapping of ar_entitlement table to resource table, it does create a column called 'resource_id' but without foreign key constraint. I need it to be pointing to id column of resource table

I tried several options, not having the belongsto specifier, using hasOne (in both domain classes) but not getting required foreign key relationship.

Any Idea what the issue is here?

log error i get is

2011-12-21 19:50:17,258 [main] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table ar_entitlement add index FKDCD6161FEFD54E5E (resourceid_id), add constraint FKDCD6161FEFD54E5E foreign key (resourceid_id) references resource (id)

Answer 1

Remove 'Resources resource' row from ArEntitlement.

static belongsTo = [resource : Resources]

Should cover that and having it defined twice might be the issue. Also you shouldn't need to specify the table names, the id generator or id column as it appears you are using the GORM defaults. Try removing these as well.

Here is the modified code...

package ars
import gra.Resources
class ArEntitlement {

    String entitlementname
    String entitlementdesc
    String entitlementcomm
    Integer departmentid
    Integer version

    static belongsTo = [resource : Resources]

    static mapping = {
    }
}