Answering my own question: you need to use computed columns: http://www.h2database.com/html/features.html#computed_columns
The updated SQL looks like this: CREATE TABLE connections (id IDENTITY, from_participant_id BIGINT NOT NULL, to_participant_id BIGINT NOT NULL, least_participant_id BIGINT AS LEAST(from_participant_id, to_participant_id), greatest_participant_id BIGINT AS GREATEST(from_participant_id, to_participant_id), UNIQUE (least_participant_id, greatest_participant_id)); Gili On Wednesday, June 19, 2013 12:52:49 PM UTC-4, Gili wrote: > > Hi, > > Is it possible to invoke functions such as LEAST or GREATEST from within a > UNIQUE constraint? I am having problems trying to implement > http://stackoverflow.com/a/16867094/14731 > > I invoked: > CREATE TABLE connections (id IDENTITY, from_participant_id BIGINT NOT > NULL, to_participant_id BIGINT NOT NULL, UNIQUE (LEAST(from_participant_id, > to_participant_id), GREATEST(from_participant_id, to_participant_id))); > > and got this error: > > org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE > CONNECTIONS (ID IDENTITY, > FROM_PARTICIPANT_ID BIGINT NOT NULL, TO_PARTICIPANT_ID BIGINT NOT NULL, > UNIQUE (LEAST([*]FROM_PARTICIPANT_ID, TO_PARTICIPANT_ID), > GREATEST(FROM_PARTICIPANT_ID, TO_PARTICIPANT_ID))) "; expected "ASC, DESC, > NULLS, ,, )"; SQL statement: trimmed [42001-172] > > What am I doing wrong? How would you implement the desired behavior under > H2? > > Thanks, > Gili > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
