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. EXAMPLEIn 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:
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:
Creating Tables in Cycles
Follow these general steps to create a table cycle:
- 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.
- 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:
EXAMPLE
- 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.
- Update or insert the values in the primary keys of the second table, the referenced table.
- Update the foreign key values of the previous table, the referencing table.
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:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |