Johannes Buchner

Like the post at [0] suggests, HSQL CHECK constraints can not be used together with CASE WHEN statements.
If you try to insert/update, you will receive a (very informative) “S1000 General error java.lang.ClassCastException”, followed by the whole SQL statement. (here I have, HSQL 1.8.0.9).

If you have something like:

ALTER TABLE personnel ADD CONSTRAINT salary_types_constraint
CHECK (
CASE
WHEN pos = 0 AND (salary < 1000 OR salary > 5000)
THEN FALSE -- personal
WHEN pos = 1 AND (salary < 10000 OR salary > 20000)
THEN FALSE -- manager
ELSE TRUE
END
);

It will not work. It will give you a “S1000 General error java.lang.ClassCastException” error message.

Replace with:

ALTER TABLE personnel ADD CONSTRAINT salary_types_constraint
CHECK (
(pos = 0 AND (salary > 1000 OR salary < 5000))
OR
(pos = 1 AND (salary < 10000 OR salary > 20000))
);

• [0] http://osdir.com/ml/java.hsqldb.user/2006/msg00278.html
• buggy report http://sourceforge.net/tracker/index.php?func=detail&aid=1913019&group_id=23316&atid=378131