Referential integrity

For a foreign key relationship to be valid, the entries in the foreign key must correspond to the primary key values of a row in the referenced table. Occasionally, some other unique column combination may be referenced instead of a primary key.

A foreign key is a reference to a primary key or UNIQUE constraint, usually in another table. When that primary key does not exist, the offending foreign key is called an orphan. SQL Anywhere automatically ensures that your database contains no rows that violate referential integrity. This process is referred to as verifying referential integrity. The database server verifies referential integrity by counting orphans.

When using a multi-column foreign key, you can determine what constitutes an orphaned row versus what constitutes a violation of referential integrity using the MATCH clause. The MATCH clause also allows you to specify uniqueness for the key, thereby eliminating the need to declare uniqueness separately.

The following is a list of MATCH types you can specify:

MATCH [ UNIQUE ] SIMPLE A match occurs for a row in the foreign key table if all the column values match the corresponding column values present in a row of the primary key table. A row is orphaned in the foreign key table if at least one column value in the foreign key is NULL.
MATCH SIMPLE is the default behavior.

If the UNIQUE keyword is specified, the referencing table can have only one match for non-NULL key values.
MATCH [ UNIQUE ] FULL A match occurs for a row in the foreign key table if none of the values are NULL and the values match the corresponding column values in a row of the primary key table. A row is orphaned if all column values in the foreign key are NULL.
If the UNIQUE keyword is specified, the referencing table can have only one match for non-NULL key values.

Example 1

The SQL Anywhere sample database contains an Employees table and a Departments table. The primary key for the Employees table is the employee ID, and the primary key for the Departments table is the department ID. In the Employees table, the department ID is called a foreign key for the Departments table because each department ID in the Employees table corresponds exactly to a department ID in the Departments table.

The foreign key relationship is a many-to-one relationship. Several entries in the Employees table have the same department ID entry, but the department ID is the primary key for the Departments table, and so is unique. If a foreign key could reference a column in the Departments table containing duplicate entries, or entries with a NULL value, there would be no way of knowing which row in the Departments table is the appropriate reference. This is prevented by defining the foreign key column as NOT NULL.

Example 2

Suppose the database also contained an office table listing office locations. The Employees table might have a foreign key for the office table that indicates which city the employee's office is in. The database designer can choose to leave an office location unassigned at the time the employee is hired, for example, either because they haven't been assigned to an office yet, or because they don't work out of an office. In this case, the foreign key can allow NULL values, and is optional.

Example 3

Execute the following statement to create a composite primary key.

CREATE TABLE pt(
     pk1 INT NOT NULL,
     pk2 INT NOT NULL,
     str VARCHAR(10)
     PRIMARY KEY ( pk1, pk2 ));

The following statements create a foreign key that has a different column order than the primary key and a different sortedness for the foreign key columns, which is used to create the foreign key index.

CREATE TABLE ft1(
     fpk INT PRIMARY KEY,
     ref1 INT,
     ref2 INT );

ALTER TABLE ft1 ADD FOREIGN KEY ( ref2 ASC, ref1 DESC)
     REFERENCES pt ( pk2, pk1 ) MATCH SIMPLE;

Execute the following statements to create a foreign key that has the same column order as the primary key but has a different sortedness for the foreign key index. The example also uses the MATCH FULL clause to specify that orphaned rows result if both columns are NULL. The UNIQUE clause enforces a one-to-one relationship between the pt table and the ft2 table for columns that are not NULL.

CREATE TABLE ft2(
     fpk INT PRIMARY KEY,
     ref1 INT,
     ref2 INT );

ALTER TABLE ft2 ADD FOREIGN KEY ( ref1, ref2 DESC )
     REFERENCES pt ( pk1, pk2 ) MATCH UNIQUE FULL;

Referential cycles

Discuss this page in DocCommentXchange.