HSQLDB - How to use custom function in check constraint

Question

First I create a simple function:

CREATE FUNCTION MY_FUNCTION(IN MY_ID BIGINT) RETURNS BOOLEAN
    SPECIFIC MY_FUNCTION_WITH_BIGINT LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA RETURNS NULL ON NULL INPUT
RETURN MY_ID IN (SELECT ID
              FROM TABLE1
              WHERE NAME IN ('name1', 'name2'));

Then I try to use it in a CHECK constraint:

ALTER TABLE TABLE2 ADD CONSTRAINT CONSTRAINT1 CHECK (MY_FUNCTION(C1) = TRUE)

I get this:

java.lang.RuntimeException: org.hsqldb.HsqlException: invalid expression in CHECK or GENERATED clause

I don't understand why, is there a way to achieve the desired effect?

Answer 1

By default, HSQLDB allows only the types of expression allowed by the SQL Standard. These expressions are very specific and guaranteed to return the same result regardless of when you check the constraint. This excludes user-defined functions and many built-in ones.

You can add a TRIGGER with the desired check. The TRIGGER is executed at the time of INSERT or UPDATE, so it does not guarantee validity in the future.