SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 293

Relationship Type CREATE TABLE Constraints

1:N relationship, parent optional Specify FOREIGN KEY constraint. Set

foreign key NULL.

1:N relationship, parent required Specify FOREIGN KEY constraint. Set

foreign key NOT NULL.

1:1 relationship, parent optional Specify FOREIGN KEY constraint.

Specify foreign key UNIQUE constraint.
Set foreign key NULL.

1:1 relationship, parent required Specify FOREIGN KEY constraint.

Specify foreign key UNIQUE constraint.
Set foreign key NOT NULL.

Figure 7-11
Summary of relationship
Definitions Using the SQL
CrEatE taBLE Statement

Casual relationship Create a foreign key column, but do not

specify FOREIGN KEY constraint. If
relationship is 1:1, specify foreign key
UNIQUE.

Figure 7-12 shows no interrelation constraints between tables. Although the SQL-92
specification defined facilities for creating such constraints, no DBMS vendor has imple-
mented those facilities. Such constraints must be implemented in triggers. An example of this
is shown later in this chapter. Figure 7-13 shows the SQL statements to create the ARTIST and
WORK tables modified with the appropriate default values and data constraints.

Implementing Default Values
Default values are created by specifying the DEFAULT keyword in the column definition just
after the NULL/NOT NULL specification. Note how in Figure 7-13 the Description column in
the WORK table is given the default value of 'Unknown provenance' using this technique.

Implementing Data Constraints
The data constraints are created using the SQL CHECK constraint. The format for the CHECK
constraint is the word CONSTRAINT followed by a developer-provided constraint name fol-
lowed by the word CHECK and then by the constraint specification in parentheses. Expressions
in CHECK constraints are akin to those used in the WHERE clause of SQL statements. Thus, the

Figure 7-12
Default Values and Data
Constraints for the View
ridge Gallery Database

Table Column Default Value Constraint

WORK Description 'Unknown

provenance'

ARTIST Nationality IN ('Candian', 'English',

'French', 'German', 'Mexican',
'Russian', 'Spainish',
'United States'.

ARTIST DateOfBirth Less than DateDeceased.

ARTIST DateOfBirth Four digits—1 or 2 is first digit,

0 to 9 for remaining three digits.

ARTIST DateDeceased Four digits—1 or 2 is first digit,

0 to 9 for remaining three digits.

TRANS SalesPrice Greater than 0 and less than

or equal to 500,000.

TRANS DateAcquired Less than or equal to DateSold.