I'm trying to formulate some check constraints in SQL Anywhere 9.0.
Basically I have schema like this:
CREATE TABLE limits (
id INT IDENTITY PRIMARY KEY,
count INT NOT NULL
CREATE TABLE sum (
my_number INT NOT NULL CHECK(my_number > 0),
PRIMARY KEY (user, limit)
I'm trying to force a constraint
my_number for each limit to be at most count in table.
CHECK ((SELECT sum(my_number) FROM sum WHERE limit = limit) <= (SELECT count FROM limits WHERE id = limit))
CHECK (((SELECT sum(my_number) FROM sum WHERE limit = limit) + my_number) <= (SELECT count FROM limits WHERE id = limit))
and they both seem not to do the correct thing. They are both off by one (meaning once you get a negative number, then insertion will fail, but not before that.
So my question is, with what version of the table are these subqueries being executed against? Is it the table before the insertion happens, or does the subquery check for consistency after the insert happens, and rolls back if it finds it invalid?