To achieve referential integrity, the database must not contain any unmatched, non-NULL foreign key values. A foreign row that violates referential integrity is called an orphan because it fails to match any primary key value in the referenced table. An orphan can be created by:

SQL Anywhere prevents referential integrity violations by preventing the creation of orphan rows.

SQL Anywhere also supports multi-column primary and foreign keys, called composite keys. With a composite foreign key, NULL values still signify the absence of a match, but how an orphan is identified depends on how referential constraints are defined in the MATCH clause.

When you create a foreign key, an index for the key is automatically created. The foreign key column order does not need to reflect the order of columns in the primary key, nor does the sorting order of the primary key index have to match the sorting order of the foreign key index. The sorting—ascending or descending—of each indexed column in the foreign key index can be customized to ensure that the sorting order of the foreign key index matches the sorting order required by specific SQL queries in your application, as specified in those statements' ORDER BY clauses. You can specify the sorting for each column when setting the foreign key constraint.

The SQL Anywhere sample database has one table holding employee information and one table holding department information. The Departments table has the following columns:

To find the name of a particular employee's department, there is no need to put the name of the employee's department into the Employees table. Instead, the Employees table contains a column, DepartmentID, holding a value that matches one of the DepartmentID values in the Departments table.

The DepartmentID column in the Employees table is a foreign key to the Departments table. A foreign key references a particular row in the table containing the corresponding primary key.

The Employees table (which contains the foreign key in the relationship) is therefore called the foreign table or referencing table. The Departments table (which contains the referenced primary key) is called the primary table or the referenced table.

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 that 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;

• FOREIGN KEY clause, CREATE TABLE statement
• Referential integrity