Database Reference
In-Depth Information
Implementing Required Parent Rows
In Chapter 6, you learned that to enforce a required parent constraint, you must define the ref-
erential integrity constraint and set the foreign key to NOT NULL in the child table. The SQL
CREATE TABLE statement for the WORK table in Figure 7-10 does both. In this case, ARTIST
is the required parent table, and WORK is the child. Thus, ArtistID in the WORK table is speci-
fied as NOT NULL (using the NOT NULL column constraint), and the ArtistFK FOREIGN KEY
table constraint is used to define the referential integrity constraint. Together, these specifica-
tions thus cause the DBMS to enforce the required parent.
If the parent were not required, then we would specify ArtistID in WORK as NULL. In that
case, WORK would not need to have a value for ArtistID and thus not need a parent. However,
the FOREIGN KEY constraint would still ensure that all values of ArtistID in WORK would be
present in the ArtistID in ARTIST.
Implementing 1:1 Relationships
SQL for implementing 1:1 relationships is almost identical to that for 1:N relationships, as just
shown. The only difference is that the foreign key must be declared as unique. For example,
if the relationship were 1:1 between ARTIST and WORK (i.e., each artist could have only one
work at the View Ridge Gallery), then in Figure 7-10 we would add the constraint:
CONSTRAINT UniqueWork UNIQUE (ArtistID)
Note that the ARTIST-to-WORK relationship in Figure 7-1 is of course not 1:1, so we will
not specify this constraint to our current SQL statements. As before, if the parent is required,
then the foreign key should be set to NOT NULL. Otherwise, it should be NULL.
Casual Relationships
Sometimes it is appropriate to create a foreign key column but not specify a FOREIGN KEY
constraint. In that case, the foreign key value may or may not match a value of the primary key
in the parent. If, for example, you define the column DepartmentName in EMPLOYEE but do
not specify a FOREIGN KEY constraint, then a row may have a value of DepartmentName that
does not match a value of DepartmentName in the DEPARTMENT table.
Such relationships, which we call casual relationships , occur frequently in applications
that process tables with missing data. For example, you might buy consumer data that include
names of consumers' employers. Assume that you have an EMPLOYER table that does not
contain all of the possible companies for which the consumers might work. You want to use
the relationship if you happen to have the values, but you do not want to require having those
values. In that case, create a casual relationship by placing the key of EMPLOYER in the con-
sumer data table but do not define a FOREIGN KEY constraint.
Figure 7-11 summarizes the techniques for creating relationships using FOREIGN KEY,
NULL, NOT NULL, and UNIQUE constraints in 1:N, 1:1, and casual relationships.
Creating Default Values and Data Constraints with SQL
Figure 7-12 shows an example set of default value and example data constraints for the View
Ridge Gallery database. The Description column in the WORK table is given the default value
of 'Unknown provenance'. The ARTIST table and TRANS tables are assigned various data
constraints.
In the ARTIST table, Nationality is limited to the values in the domain constraint shown,
and DateOfBirth is limited by the intrarelation constraint (within the same table) that
DateOfBirth occurs before DateDeceased. Further, DateOfBirth and DateDeceased, which
as noted earlier are years, are limited to the domain defined by specifying that the first digit
be a 1 or a 2 and the remaining three digits be any decimal numbers. Thus, they can have
any value between 1000 and 2999. SalesPrice in the TRANS table is limited by a range con-
straint to a value greater than 0 but less than or equal to $500,000, and PurchaseDate is lim-
ited by an intrarelation constraint that the DateSold be no earlier than the DateAcquired (i.e.,
DateAcquired is less than or equal to DateSold).
 
Search WWH ::




Custom Search