Why intended integrity constraint cannot be enforced?

What does the following integrity constraint try to enforce? (integrity constraint is primary key and check in this case but I couldn't understand what I should really write).

Explain why the intended integrity constraint cannot be enforced?

CREATE TABLE Sailors
( sid INTEGER,
  sname CHAR(10),
  rating INTEGER,
  age REAL,
  PRIMARY KEY (sid),
  CHECK
    ( (SELECT COUNT (S.sid) FROM Sailors S)
    + (SELECT COUNT (B.bid) FROM Boats B) < 100 )
) ;

We are studying SQL at school.

Sabancı bilgisayara selamlar @yguney

(Note: you have declared 2 constraints on this table, a PRIMARY KEY constraint and a CHECK constraint. The answer is about the second one.)

The CHECK constraint is trying to enforce that:

the number of rows in both tables should be less than 100

So, it should - if it worked - allow one table have 50 and the other 49 rows, or 1 and 98 or 5 and 10 or ... but none should have 100 or more. If one has 90, the other should have no more than 9 and any transaction trying to insert more rows, should be denied (raising a constraint error).

At least that's what it looks like it's trying to do. Now the problem or rather the two problems of the constraint:

-
A CHECK constraint is a row constraint, in almost all implementations. So it is checked for each row inserted or updated. This is an implementation issue and no current DBMS has implemented constraints that involve subqueries (except Firebird which claims to do so, but I haven't tested it).

There are a few DBMS that allow functions to be used in CHECK constraints (and the functions could contain subqueries), so the limitation can be "worked around" but that leads to all kinds of concurrency issues and the problem is not really solved. The advice, even in those DBMS, is not to have subqueries in CHECK constraints.

In my opinion, a constraint that involves many rows of the table (all of them in this case), should be an ASSERTION, not a CHECK constraint.

-
Second and more serious is that a CHECK constraint is a constraint on one table. It is not an ASSERTION (those are constraints that can span across multiple tables).

So even if there was an implementation that allowed CHECK constraints with subqueries, the result would be what one would expect. One way to understand why is that the constraint would only be checked when a row in Sailors would be inserted or updated. This would lead to these problematic situations:

We insert 200 rows in the Boats table. The constraint would not be checked so that would be allowed. Then we try to insert a row in Sailors. the constraint is checked and the row is forbidden. But have we managed to enforce our intention? No, because the total of the two tables is 200 rows, which is more than 100.

We insert 5 rows in Sailors. No problem so far. Then 200 rows in the Boats table. The constraint would again, as before, not be checked so that would be allowed. Then we try to update a row in Sailors. The constraint is checked and we get an error. The UPDATE fails. But we still have 205 rows in total, so why should the update fail?

As a conclusion, there are two issues with this constraint. First, that current implementations do not allow subqueries in CHECK constraints and second that it should be an ASSERTION and not a CHECK constraint, as it involves more than 1 table.

And note that none DBMS has implemented ASSERTION so far (at least those SQL based, there are a few based on Tutorial D that have.)

StackExchange Database Administrators Q#205152, answer score: 6