Filter
Top Products
Declaring entity and referential integrity
284
Example 1
The sample database contains an employee table and a department table. The
primary key for the employee table is the employee ID, and the primary key for
the department table is the department ID.
One of the items of information about each employee is the department ID of
the department to which they belong. In the employee table, the department ID
is called a
foreign key for the department table; each department ID in the
employee table corresponds exactly to a department ID in the department table.
The foreign key relationship is a many-to-one relationship. Several entries in
the employee table have the same department ID entry, but the department ID
is the primary key for the department table, and so is unique. If a foreign key
were able to reference a column in the department table containing duplicate
entries, there would be no way of knowing which of the rows in the department
table is the appropriate reference.
Example 2
Suppose the database also contained an office table, listing office locations.
The employee table might have a foreign key for the office table that indicates
where the employee’s office is located. The database designer may wish to
allow for an office location not being assigned at the time the employee is
hired. In this case, the foreign key should allow the NULL value for when the
office location is unknown or when the employee does not work out of an
office.
How you define foreign keys
Like primary keys, foreign keys are created using the CREATE TABLE
statement or ALTER TABLE statement.
For information on creating foreign keys, see “Creating primary and foreign
keys”.
Referential integrity is unenforced
Adaptive Server IQ does not enforce foreign key relationships. For this reason,
you must specify the keyword
UNENFORCED when you declare a foreign key.
IQ lets you delete a primary key that is referred to by a foreign key; it does not
produce an error or carry out any other special action you might specify.
You may wish to create a procedure that is called each time you insert or delete
data, to enforce referential integrity independently of IQ.