Databases Reference
In-Depth Information
▲
Check constraint:
limits column values to a set list, to within a specified
range, or to match a specified format. This is used for simple limits. For
example, you might want to set maximum stocking levels for inventory
items. One way to do this is through check constraints.
▲
Unique constraint:
requires that values in a column or set of columns
are unique. For example, you have an inventory table with each item's
part number used as its primary key. You might also have a separate
manufacturer part number, which is also supposed to be a unique value.
You can use a unique constraint to enforce this requirement.
▲
Default constraint:
A value entered into the column when no value is
provided. Suppose you are adding new items to the inventory file, but
you want don't necessarily want the ordering system to order items auto-
matically. You can include an OrderPoint column and use the default
constraint to give it a default value of zero. That way, if you don't enter a
value, 0 is entered for you.
You must specify a data type for each table column, though some DBMSs
allow for a default data type. You can apply nullability, check constraints, unique
constraints, and default constraints to any or all table columns as appropriate.
Addressing Entity Integrity
In most cases, entity integrity is the easiest part of ensuring database integrity.
Entity integrity is enforced through the primary key. Identifiers are specified dur-
ing initial entity and attribute design. When you convert entities to database
tables, the identifiers become the tables' primary keys.
We've already discussed different entity integrity examples. One mentioned
was the inventory table, using the part number as the primary key to enforce
entity integrity. Each item has a unique part number and is identified by that
part number.
If an entity doesn't naturally have a primary key, you need to create one arti-
ficially. This is usually done through an identity column. An identity column is
usually a numeric column, most often an integer value, generated automatically
during data entry. When you specify an identity column you specify the start-
ing value and an increment, usually a one-up count value. Some DBMSs also
support automatically generated
globally unique identifiers (GUIDs)
. These are
values that are generated in such a way that even if created for different tables
or on different servers, each value is unique.
Addressing Referential Integrity
Referential integrity, also called relational integrity, maintains relationships between
tables. Most relationships are defined and maintained through a foreign key. The
foreign key is specified as one or more columns in the referencing table. It relates
