docs.intersystems.com
Home / Using InterSystems SQL / Using Foreign Keys

Using InterSystems SQL
Using Foreign Keys
Previous section           Next section
InterSystems: The power behind what matters   
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 InterSystems SQL:
The maximum number of foreign keys for a table (class) is 400.
Foreign Key Referential Integrity Checking
By default, InterSystems IRIS 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^IRISSql16:USER] 
This checking can be suppressed system-wide using the $SYSTEM.SQL.SetFilerRefIntegrity() method. To determine the current setting, call $SYSTEM.SQL.CurrentSettings().
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, InterSystems IRIS also performs foreign key referential integrity checking on the following operations. If the specified action violates referential integrity, the command is not executed:
In a parent/child relationship there is no defined ordering of the children. Application code must not rely on any particular ordering.
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,! }


Previous section           Next section
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-04-23 13:43:22