docs.intersystems.com
Home / Using InterSystems SQL / Relationships Between Tables

Using InterSystems SQL
Relationships Between Tables
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
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. Defining this referential action using a persistent class that projects to a table is defined in the OnDelete and OnUpdate foreign key keywords in the Class Definition Reference. When creating a sharded table, these referential actions must be set to NO ACTION.
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.
Parent and Child Tables
This section provides a brief overview on defining and working with parent/child relationships. For further details, refer to the Defining and Using Relationships chapter of Defining and Using Classes.
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. For example:
INSERT INTO Sample.Invoice (Buyer,InvoiceDate) VALUES ('Fred',CURRENT_TIMESTAMP)
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)
        QUIT:(SQLCODE<0)
   &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)
   KILL tflds,SQLCODE,C1
   &sql(DECLARE C1 CURSOR FOR
        SELECT *,%TABLENAME INTO :tflds(),:tname
        FROM Sample.LineItem)
   &sql(OPEN C1)
        QUIT:(SQLCODE<0)
   &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)
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


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-07-19 06:48:23