0

I'm new to Grails and mapping and I have something that looks like this. I have two domain classes and I need to make a relationship between them, and when the relationship is done that no changes would be made to existing tables from my PostgreSQL database. 

class Insurance{

Integer id 
String osg_name
String osg_logo
String osg_email
String osg_link

static hasMany = [ insurancePackage: InsurancePackage]

static constraints = {

    id(blank: false)
    osg_name (blank: false, size: 0..155)
    osg_logo (size: 0..155)
    osg_email (blank: false, size: 0..100)
    osg_link (size: 0..155)
}



static mapping = {
    table name: "insurance", schema: "common"
    version false    
    id generator :'identity', column :'osg_id', type:'integer'

}

}

  class InsurancePackage{

    Integer id
    Integer osg_id
    String osgp_comment
    Integer tpo_id
    String osgp_link
    String osgp_label

    //static belongsTo = Insurance

    static belongsTo = [insurance: Insurance]

    static constraints = {

        id(blank: false)
        osg_id (blank: false)
        osgp_comment (blank: false, size: 0..500)
        tpo_id (blank: false,)
        osgp_link (blank: false, size: 0..155)
        osgp_label (blank: false, size: 0..10)
    }

    static mapping = {
        table name: "insurance_package", schema: 'common'
        version false
        id generator :'identity', column :'osgp_id', type:'integer'
    }

}

This is the error that I'm getting 

Error 2015-07-16 13:38:49,845 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table revoco.insurance_package add column insurance_id int4 not null
| Error 2015-07-16 13:38:49,845 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - ERROR: column "insurance_id " contains null values
| Error 2015-07-16 13:38:49,845 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table revoco.insurance_package add constraint FK684953517A89512C foreign key (insurance_id ) references revoco.insurance
| Error 2015-07-16 13:38:49,845 [localhost-startStop-1] ERROR hbm2ddl.SchemaUpdate  - ERROR: column "insurance_id " referenced in foreign key constraint does not exist

So I cant connect the two tables and I'm getting the same error, for some reason Grails are trying to find insurance_id but that is not defined in classes and they are trying to alter my tables and I don't want that to happen.

03Ronnie04
  • 31
  • 3

1 Answers1

1

You are created a new column in the insurance_package table that holds a foreign key to the insurance table. (hasMany and belongsTo --> one-to-many) The problem here is that the column has a NOT NULL contraint by default but the table appears to have already data in it.

The question is now: What to do with the data already contained in the table. Grails wants to set the NOT NULL constraint but can't because there are already in there and because you have just created the column and the values are NULL

You have 3 options depending on your use case:

  1. delete the values already contained in the table (maybe not wanted)
  2. Go in your db management tool and set a foreign key for those rows and then restart the server. The error should disappear
  3. set the constraint for your insurance reference (belongsTo) in your "InsurancePackage" object to be nullable:true
Bernhard
  • 444
  • 1
  • 4
  • 19
  • Just to go trough options that you gave me, 1. I cant delete the data in table becouse my data needs to stay preserved the way it is. 2. Same way as the first I can't change database, 3. when I declare Insurance insurance in class InsurancePackage and put in constraints insurance (nullable: true) I dont get the error but my InsurancePackage table is altered and I have new column called insurance_id and I dont want that. My Insurance and InsurancePackage tables have osg_id column and I'm trying to connect two tables trough that FK so that my data and tables look the same. – 03Ronnie04 Jul 17 '15 at 09:54
  • I understand that option 1 and 2 are not what you want. What I don't understand is that you have a new column. The column should have been there already. You need to have a column to have the FK connection between insurance and insurance_package (one-to-many). The only difference between having nullable true or false is, that the column (unsurance_id) can hold null values or can't hold null values. Please edit your question then and give us your db structure as it is at the moment and what you want to achieve – Bernhard Jul 17 '15 at 14:27