Progress
Embedded SQL-92
Guide and Reference


Referential Constraints

Sometimes specific columns in tables in two different locations in a database must be identical. The values for each row in a column or group of columns taken together must be identical to a set of columns in another table. You define the requirement that a matching row must exist in the referenced table with a referential constraint. For example, the employee number of the employee table might be in the given range, but the employee number might no longer exist in the master employee table because the employee has resigned. Use referential constraints to enforce this kind of limit.

EXAMPLE

In the next example, the value in the item_no column of the supplier_item table depends on the value in the item_no column of the item table. The item_no column of the supplier_item table references the item_no column of the item table. The item_no column is a foreign key in the supplier_item table:

EXEC SQL 
     CREATE TABLE supplier_item  
          ( 
         suppl_no  INTEGER NOT NULL PRIMARY KEY, 
         item_no   INTEGER REFERENCES item (item_no), 
         quantity  INTEGER 
          ) ; 

A foreign key is a column or combination of columns that references a primary key or a candidate key of some table. The foreign key value is either NULL or exists as the primary key value. The table that contains the foreign key is called the referencing table. The table that contains the primary or the candidate key is called the referenced table. You can specify a referential constraint at the column level or at the table level.

During INSERT or UPDATE operations on a table containing a foreign key, the database checks to determine if the foreign key value matches a corresponding primary key value. If it does not match, the operation returns an error.

During UPDATE or DELETE operations on a table containing a primary or candidate key, if the values to be deleted or updated match the foreign key of the referencing table, the operation returns an error. A value corresponding to a primary or candidate key cannot be updated or deleted if there are references to it.

When you want to drop a table containing a primary or candidate key, the database checks to see if the table has any references to it. If there are tables containing foreign keys that reference the primary or candidate keys of the table you want to drop, the operation returns an error.

Column Level Foreign Key Constraint

If a foreign key constraint specification involves only one column, you specify a column level foreign key constraint.

EXAMPLE

In the next example, item_no is the foreign key referencing the item table, and the foreign key is specified at the column level:

EXEC SQL 
     CREATE TABLE supplier_item 
           ( 
          supp_no  INTEGER NOT NULL PRIMARY KEY, 
          item_no  INTEGER NOT NULL REFERENCES item, 
          qty  INTEGER 
          ) ; 

If a foreign key references a candidate key, you must name the referenced column in a column list. If a foreign key references a primary key, the column list is optional.

EXAMPLE

The next example illustrates both conditions. In the example, invoice.item_no references the primary key of the item table. The invoice.partnum column references parts.part_no. Since parts.part_no is a primary key, the parts (part_no) column list reference in invoice.part_no is optional:

EXEC SQL 
     CREATE TABLE invoice ( 
          inv_no  INTEGER NOT NULL PRIMARY KEY, 
          item_no  INTEGER REFERENCES item, 
          part_no  CHAR(3) NOT NULL 
               REFERENCES parts (part_no), 
          qty   INTEGER NOT NULL 
     ) ; 

Table Level Foreign Key Constraint

If a foreign key constraint specification involves more than one column, you must specify the constraint at the table level.

EXAMPLE

In this example, the foreign key (empno, projno) of the hours_worked table references the primary or candidate key (empno, projno) of the assignments table:

EXEC SQL 
     CREATE TABLE hours_worked 
           ( 
          empno  INTEGER NOT NULL, 
          projno INTEGER NOT NULL, 
          date  DATE, 
          hours  TIME 
          FOREIGN KEY (empno, projno) 
               REFERENCES assignments (empno, projno) 
          ) ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095