Databases Reference
In-Depth Information
BASE TABLES ONLY: CONSTRAINTS
One thing that follows from
The Principle of Interchangeability
is that
the behavior of tables S,
LS, and NLS shouldn't depend on which if any are base tables and which if any are views. Until
further notice, therefore, let's suppose they're all base tables:
CREATE TABLE S ( ... , UNIQUE ( SNO ) ) ;
CREATE TABLE LS ( ... , UNIQUE ( SNO ) ) ;
CREATE TABLE NLS ( ... , UNIQUE ( SNO ) ) ;
Now, these tables, like all tables, are clearly subject to a number of constraints.
Unfortunately, most of those constraints are quite awkward to formulate in SQL, so I'll content
myself for present purposes with stating them in natural language only (and pretty informal
natural language at that, for the most part). Here they are:
{SNO} is a key for each of the tables; also, {SNO} in each of tables LS and NLS is a
foreign key, referencing the key {SNO} in table S.
Note:
For an explanation of why I use
braces “{” and “}” here, please refer to
SQL and Relational Theory
.
3
At any given time, table LS is equal to that restriction of table S where the CITY value is
London, and table NLS is equal to that restriction of table S where the CITY value isn't
London. Moreover, every row of table LS has CITY value London,
4
and no row of table
NLS does.
At any given time, table S is equal to the union of tables LS and NLS; moreover, that union
is
disjoint
(i.e., the corresponding intersection is empty)—no row in S appears in both LS
and NLS. To spell the point out in detail: Every row in S also appears in exactly one of LS
and NLS, and every row in either LS or NLS also appears in S.
Finally, the previous constraint and the constraint that {SNO} is a key for all three tables,
taken together, imply that every supplier number (not just every row) in S also appears in
exactly one of LS and NLS, and every supplier number in either LS or NLS also appears in
S.
Of course, as the immediately preceding bullet point illustrates, the foregoing constraints aren't
all independent of one another—some of them are logical consequences of others.
3
I remind you from the preface that throughout this topic I use “
SQL and Relational Theory”
as an abbreviated form of reference
to my topic
SQL and Relational Theory: How to Write Accurate SQL Code
(2nd edition, O'Reilly, 2012).
4
Precisely because of this fact, a more realistic version of view LS would probably drop the CITY attribute. I choose not to do
so here, in order to keep the example simple.
