Sybase 12.4.2 manual How you define foreign keys, Referential integrity is unenforced, 284

Declaring entity and referential integrity

Example 1

Example 2

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.

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.

284