Release 10.1A: OpenEdge Data Management:
SQL Development

> ## Handling cycles in referential integrity

> A cycle exists when a series of primary-key-foreign-key relationships exists within a group of tables in a database.

> In Example 5–23, 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.

> Example 5–23: Table reference cycle
>      CREATE TABLE parts
>               (
>               part_no  INTEGER NOT NULL PRIMARY KEY,
>               part_name  CHAR (19),
>               distrib_no INTEGER REFERENCES distributor
>               ) ;
>     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
>               ) ;

> 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. Example 5–24 shows this single-table cycle.

> Example 5–24: SIngle-table reference cycle
>      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

> Use the following procedure to create a table cycle.

> | 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 becomes complete. If the 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

> Use the following procedure to insert rows in a cycle.

> | 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, referenced table.

> 3. Update the foreign key values of the previous table, the referencing table.

> Example 5–25 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.

> Example 5–25: Updating data in a single-table cycle
>      CREATE TABLE employee (
>               empno INTEGER NOT NULL PRIMARY KEY,
>               ename CHAR (30) NOT NULL,
>               deptno  INTEGER NOT NULL,
>               mgr_code INTEGER REFERENCES employee (empno)
>          ) ;
>     INSERT INTO employee VALUES (100, 'JOHN', 10, NULL) ;
>     INSERT INTO employee VALUES (500, 'MARY', 30, NULL) ;
>     INSERT INTO employee VALUES (101, 'ANITA', 10, NULL) ;
>     INSERT INTO employee VALUES (501, 'ROBERT', 30, NULL) ;
>     UPDATE employee set mgr_code = 101 where empno = 100 ;
>     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 © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095