Page 70 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes The following example creates the part_sample table and specifies the part_nmbr field as the
primary key.
CREATE TABLE part_sample

(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) );

FOREIGN KEY constraints identify and enforce the relationships between tables.
A foreign key in one table points to a candidate key in another table. In the following
example, the order_part table establishes a foreign key that references the part_sample
table defined previously.
CREATE TABLE order_part
(order_nmbr int,

part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int);

GO
You cannot insert a row with a foreign key value, except NULL, if there is no candidate key
with that value. The ON DELETE clause controls what actions are taken when you try to
delete a row to which existing foreign keys point. The ON DELETE clause has the following
options:

NO ACTION specifies that the deletion fails with an error.
CASCADE specifies that all the rows with foreign keys pointing to the deleted row
are also deleted.

SET NULL specifies that all rows with foreign keys pointing to the deleted row are
set to NULL.

SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row
are set to their default value.
The ON UPDATE clause defines the actions that are taken if you try to update a candidate
key value to which existing foreign keys point. This clause also supports the NO ACTION,
CASCADE, SET NULL and SET DEFAULT options.

5.6.2 Column and Table Constraints

Constraints can be column constraints or table constraints. A column constraint is specified as
part of a column definition and applies only to that column. The constraints in the previous
examples are column constraints. A table constraint is declared independently from a column
definition and can apply to more than one column in a table. Table constraints must be used
when more than one column must be included in a constraint.

For example, if a table has two or more columns in the primary key, you must use a table
constraint to include both columns in the primary key. Consider a table that records events

64 LOVELY PROFESSIONAL UNIVERSITY