Something like:
ALTER TABLE password_tokens
ADD CONSTRAINT active_password_token_check
CHECK (
SELECT count(*) FROM password_tokens p1, password_tokens p2
WHERE p1.user_id=p2.user_id
AND p1.consumed=true
AND p1.consumed = true
AND p1.expiry>now()
AND p2.expiry>now()
) <= 1
On 2013-04-12 01:00, [email protected] wrote:
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.
--
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.
