#939 "unique constraint or index violation" inadequate message

[Nicholas Williams]

I have a table with two foreign key constraints and two unique constraints with specific different names. I am using Spring Framework's JDBC tools to add data to the table. If I add a row to the table in violation of one of the foreign keys, HSQL throws an error like this:

org.hsqldb.HsqlException: integrity constraint violation: foreign key no parent; CODE_MAP_ENTRIES_FKCODE table: LOOKUP_CODES

Spring then wraps this with a DataIntegrityViolationException from which I can extract the name of the foreign key constraint to determine what kind of error to throw of my own.

However, if I insert a row in violation of one of the unique keys, HSQL throws an error like this:

org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation

Spring appropriately wraps this in a DuplicateKeyException, however, since HSQL provides no constraint names in this error like it does with foreign key errors, I have no way of telling which unique constraint was violated. To make troubleshooting easier, the error should be changed to be:

org.hsqldb.HsqlException: integrity constraint violation: unique constraint or index violation; CODE_MAP_ENTRIES_TABLEOLDCODE table: LOOKUP_CODES

Or something similar.

I've tracked down at least where a change needs to be made to org/hsqldb/index/IndexAVL.java line 541:

throw Error.error(ErrorCode.X_23505);

This should be changed to something similar to org/hsqldb/Constraint.java line 748:

throw Error.error(null, ErrorCode.X_23503, ErrorCode.CONSTRAINT, info);

Something along the lines of:

throw Error.error(null, ErrorCode.X_23505, ErrorCode.CONSTRAINT, {
$constraintName, $tableName
});

However, I am simply not familiar enough with HSQLDB source code to be comfortable contributing a change like this to it because the table name and/or violated constraint name are not readily available within the body of that method from what I can tell. The table name isn't totally necessary, but the constraint name is definitely necessary. Every other major SQL vendor supplies the violated constraint name in the JDBC exception.

[Fred Toussi]

Thanks.

Fixed in SVN.