In SQL, you can set referential integrity constraints to ensure that the information contained in one or more columns of one table is consistent with the information contained in other columns of the same table and in other tables.
The basic concept in referential integrity is that you designate one or more columns in the referencing table as a foreign key. Items including the CCSVERSION option can also be designated as foreign keys. When a record is inserted, updated, or modified, the foreign key is then checked against a primary key in the referenced table. The number of the columns designated as a foreign key in the referencing table must match the number of the columns you designate as a primary key in the referenced table. Also, the data types of the columns designated as a foreign key must be compatible with the data types of the columns designated as a primary key.
Even though a foreign key can point to only one primary key, multiple foreign keys can point to the same primary key. If the selected items do not fit the preceding conditions, you cannot use the items to form an SQL referential integrity constraint.
| Note: | Native Enterprise Database Server applications are not affected by referential integrity constraints added to an existing Enterprise Database Server database using Relational Design Center. If you add referential constraints to an Enterprise Database Server database through Relational Design Center, the enforcement only occurs when updates, deletes, or inserts are executed through SQL statements. This might result in inconsistent data for databases updated by non-SQL applications. |
Primary Keys and Foreign Keys
An example of a primary key is an employee number used to uniquely identify an employee in a data set named EMPLOYEE. An example of a foreign key is an employee number in another structure such as PROJECT. By defining the employee number as the key in two sets that refer to the EMPLOYEE and PROJECT data sets, you can set up a logical linking mechanism. This linking mechanism enables you to traverse various data sets through the use of one data item. You can even use multiple items to set up a single logical linking mechanism.
If a data set does not have an obvious candidate for a primary key (that is, if the data set does not have an index with NO DUPLICATES specified), and if that data set contains occurring or embedded data, the occurring items and embedded data are not available through SQL.
