Using Caché SQL
Using Foreign Keys
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

To enforce referential integrity between tables you can define foreign keys. When a table containing a foreign key constraint is modified, the foreign key constraints are checked.

Defining a Foreign Key
There are several ways to define foreign keys in Caché SQL:
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:
You can define a foreign key with NOCHECK to suppress future checking of that foreign key.
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:
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,! }