Browse Prior Art Database

Optimal Elimination of Referential Constraint Checks

IP.com Disclosure Number: IPCOM000037357D
Original Publication Date: 1989-Dec-01
Included in the Prior Art Database: 2005-Jan-29
Document File: 4 page(s) / 16K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+3]

Abstract

The context of this optimization pertains to the OS/2* EE Database Manager Referential Integrity Design for the SQL Compiler component. Definitions: Access Section The compiled form of a single OS/2 EE Database Manager SQL statement. Dependent Row A row of a dependent table that has a foreign key value that matches a primary key value of its parent table. Dependent Table A table in a relationship containing a foreign key that defines the relationship.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 51% of the total text.

Page 1 of 4

Optimal Elimination of Referential Constraint Checks

The context of this optimization pertains to the OS/2* EE Database Manager Referential Integrity Design for the SQL Compiler component. Definitions: Access Section The compiled form

of a single OS/2 EE

Database Manager SQL statement.

Dependent Row A row of a dependent table that

has a foreign key value that matches

a primary

key value of its parent table.

Dependent Table A table in a relationship

containing a

foreign key that defines the

relationship.

Foreign Key A set of columns in a table whose

values

are required to match those of

a primary

key of a table unless one or

more of the

columns is null

Parent Row A row of a parent table that has

at least one dependent row.

Parent Table The table in a relationship containing

the primary key that defines the

relationship.

Primary Key A set of columns

in a table that must

contain unique, non-null values.

Referential Constraint An assertion that

non-null values of a

foreign key are valid only if they also

appear as values of the primary

key of a designated table. -

Self-referencing Table A table that is both a parent and a

dependent in the same relationship.

Background: Referential constraints must be processed at execution of an SQL INSERT, UPDATE, or DELETE statement. In the OS/2 EE Database Manager design, constraint processing is compiled into the access section during SQL statement compilation. The compiled threads are then processed during Interpretation. This processing consists of the following as per statement type:

INSERT: All non-null values of a foreign key must match those of a primary key of the parent table. If the inserted foreign key values do not match those of the foreign key's primary key, the INSERT is not permitted.

UPDATE: All non-null values of a foreign key after UPDATE must match those of a primary key of the parent table. If the updated foreign key values do

1

Page 2 of 4

not match those of the foreign key's primary key, the UPDATE is not permitted. An UPDATE of the primary key of a parent row is not permitted.

DELETE:

If the constraint's DELETE RULE is CASCADE

The DELETE of a parent row is propagated to its

dependent rows.

If the constraint's DELETE RULE is RESTRICT

The DELETE is not permitted if a row in the parent

table being deleted has dependent rows.

If the constraint's DELETE RULE is SET NULL

For each parent row deleted, each nullable column

of the

foreign key in each dependent row is set to null.

Example Tables to Illustrate Optimization: CREATE TABLE Departments (Dept CHAR(3) NOT NULL PRIMARY KEY, Dept_Name VARCHAR(40), . . )

CREATE TABLE Employees (Lastname CHAR(20), Salary DECIMAL(8,2), Dept CHAR(3) REFERENCES Departs,
. . ) CREATE TABLE Personnel (Lastname CHAR(20), Employee_ID INTEGER NOT NULL PRIMARY KEY, Manager_ID INTEGER REFERENCES Personnel ON DELETE CASCADE, .
. ) Optimization:

In the OS/2 EE Database Manager design, a specific referential constraint is not included in the compiled access s...