Progress
Embedded SQL-92
Guide and Reference


Handling Cycles in Referential Integrity

A cycle is a specific relationship between base tables. A cycle exists when there is a list of two or more base tables, and the first table has a foreign key that references the second table, the second table has a foreign key that references the third table and so on, and the last table has a foreign key that references the first table.

CAUTION: Release 9.0B of Progress SQL-92 does not supply a mechanism for dropping a cycle. You can create a deadlock situation if you define tables with referential integrity constraints in a cycle. Create these in a test database only. The only mechanism for dropping these tables is to delete the database.
EXAMPLE

In the next example, the parts.distrib_no column references the primary key of the distributor table, and the distributor.part_no column references the primary key of the parts table. Each of the tables references the other, forming a cycle:

EXEC SQL 
     CREATE TABLE parts 
          ( 
          part_no  INTEGER NOT NULL PRIMARY KEY, 
          part_name  CHAR (19), 
          distrib_no INTEGER REFERENCES distributor 
          ) ; 
EXEC SQL 
     CREATE TABLE distributor 
          ( 
          distrib_no  INTEGER NOT NULL PRIMARY KEY, 
          distrib_name  CHAR (19), 
          address   CHAR (30), 
          phone_no  CHAR (10), 
          part_no   INTEGER REFERENCES parts 
          ) ; 

EXAMPLE

A special case of the cycle in referential integrity occurs when a foreign key of a table references the primary key of the same table. The following example shows this single-table cycle:

EXEC SQL 
     CREATE TABLE employee 
          ( 
          empno INTEGER NOT NULL PRIMARY KEY, 
          ename CHAR (30) NOT NULL, 
          deptno  INTEGER NOT NULL, 
          mgr_code INTEGER REFERENCES employee(empno) 
          ) ; 

Creating Tables in Cycles

Follow these general steps to create a table cycle:

  1. Create the first table with a reference to a table that is not yet created. Although the CREATE TABLE succeeds, it is marked incomplete. The INSERT, UPDATE, SELECT, and DELETE operations are not allowed on an incomplete table.
  2. Create the referenced table with a primary or candidate key. The definition of the referencing table, the first table, becomes complete. If this second table also contains a foreign key that references a table that is not yet created, this second table is also marked incomplete. This process continues until you create the last table.
Inserting Rows in a Cycle

Follow these general steps to insert rows into tables that form a cycle:

  1. Insert rows into one of the tables that forms the cycle, with NULL values in the foreign key columns. If the foreign key is NULL, the database does not check for a match between the foreign key and the corresponding primary key. The insert succeeds. This is the referencing table.
  2. Update or insert the values in the primary keys of the second table, the referenced table.
  3. Update the foreign key values of the previous table, the referencing table.
EXAMPLE

The next example shows how to insert or update values into the employee table. This table forms a single-table cycle. First insert NULL into the mgr_code column. After you insert rows, update the values of the mgr_code column:

EXEC SQL 
     CREATE TABLE employee ( 
          empno INTEGER NOT NULL PRIMARY KEY, 
          ename CHAR (30) NOT NULL, 
          deptno  INTEGER NOT NULL, 
          mgr_code INTEGER REFERENCES employee (empno) 
     ) ; 
EXEC SQL 
     INSERT INTO employee VALUES (100, 'JOHN', 10, NULL) ; 
EXEC SQL 
     INSERT INTO employee VALUES (500, 'MARY', 30, NULL) ; 
EXEC SQL 
     INSERT INTO employee VALUES (101, 'ANITA', 10, NULL) ; 
EXEC SQL 
     INSERT INTO employee VALUES (501, 'ROBERT', 30, NULL) ; 
EXEC SQL 
     UPDATE employee set mgr_code = 101 where empno = 100 ; 
EXEC SQL 
     UPDATE employee set mgr_code = 501 where empno = 500 ; 
/* 
** Anita is John's manager. 
** John's employee row references Anita's employee row. 
** Robert is Mary's manager. 
** Mary's employee row references Robert's employee row. 
** The mgr_code is still NULL in Anita's row and in Robert's row. 
** To set the mgr_code in Anita's row and Robert's row: 
**      1. Insert rows for Anita's manager and Robert's manager 
**      2. Update Anita's row and Robert's row 
*/ 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095