Database Reference
In-Depth Information
Primary key constraints cannot be NULL and will force the addition
of a NOT NULL constraint.
Foreign key constraints can be nullable. A foreign key constraint
should only be nullable where a row in the foreign key table does not
have to have a parent row in a primary key table. This somewhat
negates the use of Referential Integrity in the first place. However,
data warehouse fact tables and object structures sometimes do this
sort of thing. I do not recommend allowing foreign key columns to
be nullable.
20.2.1.3
Check Constraints
A check constraint applies a condition to an expression of a row, such as
checking a column value. If the result is not TRUE and evaluated as a null
value, then an error results and the SQL statement involving the row fails.
Typically, a check constraint can be used for validation of a column in a row
or a check across multiple columns in the same row.
A check constraint can be inline or out-of-line. An inline check con-
straint must apply as a check on an individual column, whereas an out-of-
line check constraint could apply to a single column or multiple columns.
Let's go ahead and create a copy of the ARTISTS table again and demon-
strate some check constraints. We want to start by dropping the ARTISTS
table copy of the ARTIST table first. Because the ARTISTS table is linked
to the SONGS table by a referential relationship using primary and foreign
keys, and the SONGS table is in turn linked to the CDTRACKS table,
simply dropping the ARTISTS table will produce an error. Thus we have to
drop all three tables in the correct sequence, from the bottom of the refer-
ential hierarchy, upward.
DROP TABLE CDTRACKS;
DROP TABLE SONGS;
DROP TABLE ARTISTS;
Note:
We could have used the DROP TABLE ... CASCADE CON-
STRAINTS statement to drop only the ARTISTS table and all related con-
straints in other tables. This would result in only the ARTISTS table being
dropped. We would still have to drop the other tables. Avoiding the CAS-
CADE option is cleaner and perhaps a little more logically safe because it
does not use brute force!
