Skip to main content

Relationships Between Tables

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:

A RowID field used as a foreign key reference must be public. Refer to RowID Hidden? for how to define a table with a public (or private) RowID field.

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

Foreign Key Referential Integrity Checking

A foreign key constraint can specify a referential action on update or on delete. Defining this referential action using DDL is described in CREATE TABLE Referential Action Clause. When creating a sharded table, these referential actions must be set to NO ACTION.

By default, InterSystems IRIS® data platform 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] 

If you use other means to populate a table with data, referential integrity constraints may not be enforced. If this is a concern you should validate the table data.

By default, when a row with a foreign key is deleted, InterSystems IRIS acquires a long term (until the transaction ends) shared lock on the corresponding referenced table's row. This prevents an update or delete of the referenced row until the DELETE transaction on the referencing row completes. This prevents the situation where the referenced row is deleted and then the delete of the referencing row is rolled back. If that happened, the foreign key would reference a non-existent row. This lock is not acquired if the foreign key is defined with NoCheck, or if the DELETE of the referencing row is specified with %NOCHECK or %NOLOCK.

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:

  • 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.

In a parent/child relationship there is no defined ordering of the children. Application code must not rely on any particular ordering.

Defining Referential Integrity Checking with Persistent Classes

Defining a referential action on update or delete using a persistent class that projects to a table is defined in the OnDelete and OnUpdate foreign key keywords.

Referential integrity checking can be suppressed system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("FilerRefIntegrity",0,.oldval). The default is 1 (referential integrity checking performed). To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

Parent and Child Tables

This section provides a brief overview on defining and working with parent/child relationships through persistent classes. For further details, see Defining and Using Relationships.

Defining Parent and Child Tables

When defining persistent classes that project to tables you can specify a parent/child relationship between two tables using the Relationship property.

The following example defines the parent table:

Class Sample.Invoice Extends %Persistent 
{
  Property Buyer As %String(MAXLEN=50) [Required];
  Property InvoiceDate As %TimeStamp;
  Relationship Pchildren AS Sample.LineItem [ Cardinality = children, Inverse = Cparent ];
}

The following example defines a child table:

Class Sample.LineItem Extends %Persistent 
{
  Property ProductSKU As %String;
  Property UnitPrice As %Numeric;
  Relationship Cparent AS Sample.Invoice [ Cardinality = parent, Inverse = Pchildren ];
}

In the Management Portal SQL interface Catalog Details tab, the Table Info provides the name of the Child Table(s) and/or the Parent Table. If a child table, it provides references to the parent table, such as Cparent->Sample.Invoice.

A child table can itself be the parent of a child table. (This child of a child is known as a “grandchild” table.) In this case, the Table Info provides the names of both the Parent Table and the Child Table.

Inserting Data into Parent and Child Tables

You must insert each record into the parent table before inserting the corresponding records in the child table. To find the ROWID of the record in the parent table, select the %ID column and use a WHERE clause to specify the newly added record. For example:

INSERT INTO Sample.Invoice (Buyer,InvoiceDate) VALUES ('Fred',CURRENT_TIMESTAMP)
SELECT %ID FROM Sample.Invoice WHERE Buyer = 'Fred' // returns 1
INSERT INTO Sample.LineItem (Cparent,ProductSKU,UnitPrice) VALUES (1,'45-A7',99.95)
INSERT INTO Sample.LineItem (Cparent,ProductSKU,UnitPrice) VALUES (1,'22-A1',0.75)

Attempting to insert a child record for which no corresponding parent record ID exists generates an SQLCODE -104 error with a %msg Child table 'Sample.LineItem' references non-existent row in parent table.

During an INSERT operation on a child table, a shared lock is acquired on the corresponding row in the parent table. This row is locked while inserting the child table row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced parent row is not changed during the insert operation.

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 (Cparent), with the format parentref, Subscript 1 is set to the child record ID with the format parentref||childref. In a parent table, Subscript 0 is undefined. This is shown in the following examples:

   KILL tflds,SQLCODE,C1
   &sql(DECLARE C1 CURSOR FOR
        SELECT *,%TABLENAME INTO :tflds(),:tname
        FROM Sample.Invoice)
   &sql(OPEN C1)
        IF SQLCODE<0 {WRITE "Serious SQL Error:",SQLCODE," ",%msg  QUIT}
    &sql(FETCH C1)
       IF SQLCODE=100 {WRITE "The ",tname," table contains no data",!  QUIT}
       WHILE $DATA(tflds(0)) {
                              WRITE tname," is a child table",!,"parent ref: ",tflds(0)," %ID: ",tflds(1),!
                              &sql(FETCH C1)
                              IF SQLCODE=100 {QUIT}
                             }
      IF $DATA(tflds(0))=0 {WRITE tname," is a parent table",!}
    &sql(CLOSE C1)
      IF SQLCODE<0 {WRITE "Error closing cursor:",SQLCODE," ",%msg  QUIT}
   KILL tflds,SQLCODE,C1
   &sql(DECLARE C1 CURSOR FOR
        SELECT *,%TABLENAME INTO :tflds(),:tname
        FROM Sample.LineItem)
   &sql(OPEN C1)
       IF SQLCODE<0 {WRITE "Serious SQL Error:",SQLCODE," ",%msg  QUIT}
   &sql(FETCH C1)
       IF SQLCODE=100 {WRITE "The ",tname," table contains no data",! QUIT}
       WHILE $DATA(tflds(0)) {
                              WRITE tname," is a child table",!,"parent ref: ",tflds(0)," %ID: ",tflds(1),!
                              &sql(FETCH C1)
                              IF SQLCODE=100 {QUIT}
                             }
      IF $DATA(tflds(0))=0 {WRITE tname," is a parent table",!}
  &sql(CLOSE C1)
    IF SQLCODE<0 {WRITE "Error closing cursor:",SQLCODE," ",%msg  QUIT}

For a child table, tflds(0) and tflds(1) return values such as the following:

parent ref: 1 %ID: 1||1
parent ref: 1 %ID: 1||2
parent ref: 1 %ID: 1||3
parent ref: 1 %ID: 1||9
parent ref: 2 %ID: 2||4
parent ref: 2 %ID: 2||5
parent ref: 2 %ID: 2||6
parent ref: 2 %ID: 2||7
parent ref: 2 %ID: 2||8

For a “grandchild” table (a table that is the child of a child table), tflds(0) and tflds(1) return values such as the following:

parent ref: 1||1 %ID: 1||1||1
parent ref: 1||1 %ID: 1||1||7
parent ref: 1||1 %ID: 1||1||8
parent ref: 1||2 %ID: 1||2||2
parent ref: 1||2 %ID: 1||2||3
parent ref: 1||2 %ID: 1||2||4
parent ref: 1||2 %ID: 1||2||5
parent ref: 1||2 %ID: 1||2||6
FeedbackOpens in a new tab