To recap, a database constraint constrains the values that can appear in a given database. In Tutorial D, such constraints are specified by means of a CONSTRAINT statement (or by some shorthand that's effectively equivalent to such a statement); in SQL, they're specified by means of a CREATE ASSERTION statement (or, again, by some equivalent shorthand). I don't want to get into details of those shorthands at least, not yet because they're really just a matter of syntax; for now, let's stay with the "longhand" forms. I'll begin with a series of examples (Tutorial D on the left and SQL on the right, as usual). Example 1Status values must be in the range 1 to 100 inclusive: CONSTRAINT C1 IS_EMPTY | CREATE ASSERTION C1 CHECK ( S WHERE STATUS < 1 | ( NOT EXISTS OR STATUS > 100 ) ; | ( SELECT S.* FROM S | WHERE S.STATUS < 1 | OR S.STATUS > 100 ) ) ; This constraint involves just a single attribute of a single relvar; as a consequence, it can be checked for a given supplier tuple by examining just that tuple in isolation there's no need to look at any other tuples in the relvar or any other relvars in the database. (For this reason, such constraints are sometimes referred to, informally, as tuple constraints.) Example 2Suppliers in London must have status 20: CONSTRAINT C2 IS_EMPTY | CREATE ASSERTION C2 CHECK ( S WHERE CITY = 'London' | ( NOT EXISTS AND STATUS 20 ) ; | ( SELECT S.* FROM S | WHERE S.CITY = 'London' | AND S.STATUS <> 20 ) ) ; This constraint involves two distinct attributes (of the same relvar), but it's still the case, as it was with constraint C1, that it can be checked for a given supplier tuple by examining just that tuple in isolation. Example 3No two tuples in relvar S have the same supplier number (in other words, {SNO} is a key for that relvar):[*]
CONSTRAINT C3 | CREATE ASSERTION C3 CHECK COUNT ( S ) = | ( UNIQUE ( SELECT S.SNO COUNT ( S { SNO } ) ; | FROM S ) ) ; Like constraints C1 and C2, this constraint still involves just one relvar; however, it clearly can't be checked for a given supplier tuple by examining just that tuple in isolation. Of course, it's very unlikely in practice that constraint C3 would be specified in longhand as shown some kind of explicit KEY shorthand is obviously to be preferred. I give the longhand form merely to make the point that such shorthands are indeed, in the final analysis, just shorthands. By the way, the SQL formulation of constraint C3 needs a word of explanation. UNIQUE is an SQL operator that returns TRUE if and only if every row within its argument table is distinct; the UNIQUE invocation in the constraint thus returns TRUE if and only if no two rows in table S have the same supplier number. But note that if I had followed my preferred discipline of always specifying DISTINCT, thus: UNIQUE ( SELECT DISTINCT S.SNO FROM S ) then the UNIQUE couldn't possibly return FALSE! In fact, UNIQUE makes no sense from a relational point of view; it's needed in SQL only because SQL tables aren't relations, in general. Here for interest is an SQL formulation of constraint C3 that more closely resembles the Tutorial D formulation: CREATE ASSERTION C3 CHECK ( ( SELECT COUNT ( * ) FROM S ) = ( SELECT COUNT ( SNO ) FROM S ) ) ; Example 4No supplier with status less than 20 can supply part P6: CONSTRAINT C4 IS_EMPTY | CREATE ASSERTION C4 CHECK ( ( S JOIN SP ) | ( NOT EXISTS WHERE STATUS < 20 | ( SELECT * AND PNO = PNO('P6') ) ; | FROM S, SP | WHERE S.SNO = SP.SNO | AND S.STATUS < 20 | AND SP.PNO = PNO('P6') ) ) ; This constraint involves in fact, interrelates two distinct relvars, S and SP; in general, of course, a database constraint might involve or interrelate any number of distinct relvars. NOTE Terminology: A constraint that involves just a single relvar is known, informally, as a relvar constraint (sometimes a single- relvar constraint, for emphasis). A constraint that involves two or more distinct relvars is known, informally, as a multi-relvar constraint. Example 5Every supplier number in relvar SP must appear in relvar S: CONSTRAINT C5 | CREATE ASSERTION C5 CHECK SP { SNO } S { SNO } ; | ( NOT EXISTS | ( SELECT SP.SNO | FROM SP | EXCEPT | SELECT S.SNO | FROM S ) ) ; As you can see, the Tutorial D formulation of this contraint involves a relational comparison. SQL doesn't support relational comparisons, however, and so we have to indulge in some circumlocution in the SQL formulation. Of course, given that {SNO} is a key in fact, the key for relvar S, it's clear that constraint C5 is basically just the foreign key constraint from SP to S. The usual FOREIGN KEY syntax can thus be regarded as shorthand for constraints like C5. So when are database constraints checked? Conventional wisdom has it that single-relvar constraint checking is immediate (meaning it's done whenever the relvar in question is updated), while multi-relvar constraint checking is deferred to end-of-transaction ("commit time"). I want to argue, however, that all database constraint checking must be immediate, and deferred checking which is supported in the SQL standard, and indeed in some SQL products is a logical mistake. In order to explain this unorthodox view, I need to digress for a moment to discuss transactions. |