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 h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
