CODEWITHSUNDEEP
javahibernatehsqldb
Mohammad Khazab
Mohammad Khazab

Reputation: 1

Error: integrity constraint violation: NOT NULL check constraint in Hibernate

So here's the issue:

I have a ConfigGlobal class that is a parent class, and ConfigNetwork and ConfigProject extend it. They are marked by hibernate annotation as I want a database table for each of these three classes. 

@Entity
@Table(name = "CONFIG_GLOBAL")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class ConfigGlobal implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "CONFIG_ID")
    private Long configId;

    @OneToOne
    @JoinColumn(name = "CONFIG_PROPERTY_ID")
    private ConfigProperty configProperty;

    @Column(name = "VALUE")
    private String value;

    public ConfigGlobal() {

    }

    public ConfigGlobal(ConfigProperty configProperty, String value) {
        this.configProperty = Preconditions.checkNotNull(configProperty);
        this.value = Preconditions.checkNotNull(value);
    }

    //getter and setter methods

}

The initial issue was that the parent class should have an autoincrement primary key but using GenerationType as "AUTO" or "IDENTITY" gives error:

Cannot use identity column key generation with <union-subclass> mapping for:   com.db.model.ConfigGlobal

According to this Thread, the problem here is that "table-per-class" inheritance and GenerationType.Auto. And it should use GenerationType.TABLE as explained in this Thread In a "table-per-class" strategy you use one table per class and each one has an ID. To have unique ids through an inheritance hierarchy (which JPA requires), it cannot be done with TABLE_PER_CLASS and IDENTITY since IDENTITY works off a table, and there are now multiple "root" tables in the inheritance hierarchy... Therefore, this is fixed by using GenerationType.TABLE instead of GenerationType.IDENTITY:

@Entity
@Table(name = "CONFIG_GLOBAL")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class ConfigGlobal implements Serializable {
     ... 
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    @Column(name = "CONFIG_ID")
    private Long configId;
    ...
}

But here's my issue now: I have a script that should insert initial values into the ConfigGlobal table using SQL query. After the tables are created by Hibernate, I run Maven to insert the values at start (Database is empty). The SQL query is as follows:

insert into CONFIG_GLOBAL (CONFIG_ID, VALUE, CONFIG_PROPERTY_ID) values (default, 'all',  (select CONFIG_PROPERTY_ID from CONFIG_PROPERTY where NAME='accept-connections-from'))

After running it I get this error:

Error: integrity constraint violation: NOT NULL check constraint; SYS_CT_10093 table: CONFIG_GLOBAL column: CONFIG_ID

When I change default keyword in the SQL query to an int (e.g. '1') the problem is fixed, but I don't want to manually increment the IDs as there are many config values and it's hard to maintain in future.

I'm using HSQL by the way.

What do you suggest I do to fix this issue?

Really appreciate your help.

Upvotes: 0

Views: 2075

Answers (1)

Ben Damer
Ben Damer

Reputation: 1036

You can change the query that performs the initial insert to increment the ID for each new row:

insert into CONFIG_GLOBAL (CONFIG_ID, VALUE, CONFIG_PROPERTY_ID) 
  select ROWNUM(), 'all', CONFIG_PROPERTY_ID from CONFIG_PROPERTY where NAME ='accept-connections-from';

Note that the ROWNUM function is only supported by HSQLDB 2.2.x and later.

Upvotes: 0

Related Questions