Why intended integrity constraint cannot be enforced?

Question

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

Answer 1

^{(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.)

Answer 2

Not sure I understand your question, but I'll give it a try anyhow. CHECK constraints containing SELECT is valid according to standard (see F671 in for example http://jtc1sc32.org/doc/N1201-1250/32N1208-TC9075-P02-foundation.pdf), but very few DBMS:s supports this. Firebird claims to have this feature

http://www.firebirdsql.org/en/sql-conformance/.

SQL-server does not directly support it, but you can hide the select in a function, and use the function in the constraint.

Other than that, I don't know.

As others have pointed out in this thread the construction is problematic from an implementation perspective, but it is not without merits. "Sub-typing" in RDBM:s is often implemented as:

CREATE TABLE Parent
( key ... not null primary key
, type ... not null
,    constraint ak1 unique (key, type)
,    check (type in ...));

CREATE TABLE child1
( key not null primary key
, type ... not null
,    constraint fk1 foreign key (key, type) references parent (key, type)
,    constraint c1 check (type = ...));

ak1 ^ fk1 ^ c1 is a type control that guarantees that parent.type is consistent with any rows inserted into child1. It can however be argued that having to declare the type in child1 is unnecesary and redundant, as is having to declare the redundant unique constraint ak1. Allowing sub-selects in a constraint allows for a more elegant solution:

CREATE TABLE Parent
( key ... not null primary key
, type ... not null
,    check (type in ...));

CREATE TABLE child1
( key not null primary key
,    constraint fk1 foreign key (key) references parent (key, type)
,    constraint c1 check 
         ((select type from parent p where p.key = key...) = ...);

No need for type in child1 and a redundant ak1 key in parent. From a theoretical relational point of view, ak1 is reducable (it is a superkey of the primary key), and therefore questionable as an alternative key.

Answer 3

This constraint checks total amount of records in Sailors and Boats tables is less than 100. But it is checked only for one of tables (CHECK CONSTRAINT is table constraint. It has table scope, so no actions performed on another table data will trigger that check.), there is nothing to prevent adding records to Boats, result in that constraint fails.

If so, the only operations You can perform on Sailors table are SELECT or DELETE, and CREATE/UPDATE operations will fail with a CHECK CONSTRAINT error.

To perform CREATE/UPDATE on Sailors you are to delete some records from one or both tables. To prevent it you are add the same constraint to Boats table too.