Using Foreign Keys

Defining a Foreign Key

There are several ways to define foreign keys in Caché SQL:

• You can define a relationship between two classes. Defining a relationship automatically projects a foreign key constraint to SQL. For more information on relationships, see Using Caché Objects.

• You can add an explicit foreign key definition to a class definition (for cases not covered by relationships). For information, see “Foreign Key Definitions” in the Caché Class Definition Reference.

• You can add a foreign key using the CREATE TABLE or ALTER TABLE command. You can remove a foreign key using the ALTER TABLE command. These commands are described in the Caché SQL Reference.

The maximum number of foreign keys for a table (class) is 400.

Foreign Key Referential Integrity Checking

By default, Caché performs foreign key referential integrity checking on INSERT, UPDATE and DELETE operations. If the operation would violate referential integrity, it is not performed; the operation issues an SQLCODE -121, -122, -123, or -124 error. A failed referential integrity check generates an error such as the following:

ERROR #5540: SQLCODE: -124 Message: At least 1 Row exists in table 'HealthLanguage.FKey2' 
which references key NewIndex1 - Foreign Key Constraint 'NewForeignKey1' (Field 'Pointer1') 
failed on referential action of NO ACTION [Execute+5^CacheSql16:USER] 

This checking can be suppressed systemwide using either of the following:

• Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings (System, Configuration, General SQL Settings). On this screen you can view the current setting of Perform Referential Integrity Checks on Foreign Keys for INSERT, UPDATE, and DELETE. The default is “Yes”.

• Invoke the $SYSTEM.SQL.SetFilerRefIntegrity()Opens in a new tab method.

When using a persistent class definition to define a table, you can define a foreign key with the NoCheck keyword to suppress future checking of that foreign key. CREATE TABLE does not provide this keyword option.

You can suppress checking for a specific operation by using the %NOCHECK keyword option.

By default, Caché also performs foreign key referential integrity checking on the following operations. If the specified action violates referential integrity, the command is not executed:

• ALTER TABLE DROP COLUMN.

• ALTER TABLE DROP CONSTRAINT. Issues SQLCODE -317. Foreign Key integrity checking can be suppressed using SET OPTION COMPILEMODE=NOCHECK.

• DROP TABLE. Issues SQLCODE -320. Foreign Key integrity checking can be suppressed using SET OPTION COMPILEMODE=NOCHECK.

• TRUNCATE TABLE (same considerations as DELETE).

• Trigger events, including BEFORE events. For example, a BEFORE DELETE trigger is not executed if the DELETE operation would not be performed because it violates foreign key referential integrity.

Identifying Parent and Child Tables

In Embedded SQL, you can use a host variable array to identify parent and child tables. In a child table, Subscript 0 of the host variable array is set to the parent reference, with the format parentref or parentref||childref. In a parent table, Subscript 0 is undefined. This is shown in the following examples:

  &sql(SELECT *,%TABLENAME INTO :tflds(),:tname
        FROM Aviation.Event )
   IF SQLCODE=0 {
       IF $DATA(tflds(0)) {
       WRITE tname," is a child table",!,"parent ref: ",tflds(0),! }
       ELSE {WRITE tname," is a parent table",! }
   }
   ELSE {WRITE "SQLCODE error=",SQLCODE,! }

  &sql(SELECT *,%TABLENAME INTO :tflds(),:tname
        FROM Aviation.Aircraft )
   IF SQLCODE=0 {
       IF $DATA(tflds(0)) {
       WRITE tname," is a child table",!,"parent ref: ",tflds(0),! }
       ELSE {WRITE tname," is a parent table",! }
   }
   ELSE {WRITE "SQLCODE error=",SQLCODE,! }

  &sql(SELECT *,%TABLENAME INTO :tflds(),:tname
        FROM Aviation.Crew )
   IF SQLCODE=0 {
       IF $DATA(tflds(0)) {
       WRITE tname," is a child table",!,"parent ref: ",tflds(0),! }
       ELSE {WRITE tname," is a parent table",! }
   }
   ELSE {WRITE "SQLCODE error=",SQLCODE,! }