0

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 (
    user INT,
    limit INT,
    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.

I've tried

CHECK ((SELECT sum(my_number) FROM sum WHERE limit = limit) <= (SELECT count FROM limits WHERE id = limit))

and

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?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Alex Koay
  • 2,915
  • 4
  • 18
  • 16

1 Answers1

0

I do not really understand what you try to enforce here but based on this help topic.

Using CHECK constraints on columns

Once a CHECK condition is in place, future values are evaluated against the condition before a row is modified.

I would go for a before insert trigger. You have more options and can bring up a better error message.

TDuemesnil
  • 36
  • 1