Hi, As far as I know, such constraints are not supported by any database except for H2. The constraint is checked before inserting the row, but I don't consider this to be a bug.
Regards, Thomas On Thursday, March 14, 2013, Rami Ojares wrote: > Hi, > > Constraints seem to be checked before making the change which is wrong. > > Test case: > -- Create table with one boolean column > CREATE TABLE T1 ( A BOOLEAN NOT NULL ); > -- Add a constraint that ensures that the table can contain at most 2 TRUE > values > ALTER TABLE T1 ADD CONSTRAINT C1 CHECK ( > ( SELECT SUM(CAST(A AS INT)) FROM T1) < 3 > ); > -- Insert 3 TRUE values in single pass. Should give an error but does not > INSERT INTO T1 VALUES (TRUE), (TRUE), (TRUE) > -- Now inserting false which never should fail gives an error because the > table is in inconsistent state > INSERT INTO T1 VALUES (FALSE) > > The same problem occurs when inserting the true values one by one. > The third succeeds making the check constraint always fail before deleting > at least one true value. > > So deducing from this behaviour it seems that the constraint check is done > before the changes which of course makes no sense. > > - Rami > > -- > 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?hl=en<http://groups.google.com/group/h2-database?hl=en> > . > For more options, visit > https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> > . > > > -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
