Hello I'm trying to port some things over from a postgres database we have. I'm stuck on a check constraint on one of our postgres tables. Basically I need to see if any other rows in the table would prevent the current one from being inserted.
In this case, two rows cannot * share a user id, * have "consumed" true, AND * have an expiry date some time in the future. in postgres, I'd use a function, and pass values from the row into the function, which would perform a SELECT over the table to see if any existing rows would prevent this row from being inserted. CREATE OR REPLACE FUNCTION check_password_reset_token(int) RETURNS boolean AS ' SELECT count(*)>0 FROM password_tokens WHERE (user_id=$1 AND consumed=true AND expiry>now()); ' LANGUAGE SQL; ALTER TABLE password_tokens ADD CONSTRAINT active_password_token_check CHECK (check_password_reset_token(user_id)); When I try the above code in H2, it complains about not understanding functions, but when i try to move the whole thing into the check constraint in the alter table statement, then I get stuck on actually passing the row's values into the parts of the SELECT up there. I know h2 supports Java functions, so that's a last resort, but we're only using h2 for in-memory testing and I don't want to have to test the test code, as it were. Is there another way to perform the same check constraint in h2? -- 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.
