Search code examples
javaindexingconstraintsuniquehsqldb

HSQLDB: REPLACE INTO table throws org.hsqldb.HsqlException: integrity constraint violation:

I am using HSQLDB on my javafx application, I have already set the

sql.syntax_mys=true

REPLACE INTO works ok WHEN the duplicate field is PRIMARY KEY or a single UNIQUE INDEXED FIELD.

the problem comes when the duplicated values are part of UNIQUE INDEX on multiple columns instead of replacing the rows, it throws the integrity constraint violation: 

CREATE UNIQUE INDEX table_unique_index ON table (COLUMN1, COLUMN2, COLUMN3);
REPLACE INTO table (COLUMN1, COLUMN2, COLUMN3, COL4, COL5) VALUES ('VAL1', 'VAL2', 'VAL3', 4, 5, 6);
REPLACE INTO table (COLUMN1, COLUMN2, COLUMN3, COL4, COL5) VALUES ('VAL1', 'VAL2', 'VAL3', 3, 6, 3); 

java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation: FEE_STRUCTURE_0
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    at org.snowbit.databases.HSQLDB.execute(HSQLDB.java:142)
    at org.snowbit.databases.HSQLDB.execute(HSQLDB.java:137)

I have searched for a solution or for someone with a similar problem and have found none.. please assist thank you in advance.

Solution

  • Create a unique constraint on multiple columns instead of creating a unique index. You can do this in both MySQL and HSQLDB.

    ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (COLUMN1, COLUMN2, COLUMN3);