This section provides a brief overview on defining and working with parent/child relationships. 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
