Defining Relationships Among Tables
You can use referential integrity (RI) with Pervasive PSQL to define how each table is related to other tables in the database. RI assures that when a column (or group of columns) in one table refers to a column (or group of columns) in another table, changes to those columns are synchronized. RI provides a set of rules that define the relationships between tables. These rules are known as referential constraints. (Referential constraints are also informally referred to as relationships.)
When you define referential constraints for tables in a database, the transactional database engine enforces the constraints across all applications that access those tables. This frees the applications from checking table references independently each time an application changes a table.
You must name your database in order to use RI. Once you have defined referential constraints, each affected data file contains the database name. When someone attempts to update a file, the transactional database engine uses the database name to locate the data dictionary containing the applicable RI definitions and checks the update against those RI constraints. This prevents Pervasive PSQL applications from compromising RI, since the transactional database engine blocks updates that do not meet referential constraints.
To define referential constraints on the tables in a database, use CREATE TABLE and ALTER TABLE statements. Refer to the following topics in SQL Engine Reference for the syntax of these statements:
•
CREATE TABLE
•
ALTER TABLE
Referential Integrity Definitions
The following definitions are useful in understanding referential integrity.
•
A parent table is a table that contains a primary key referenced by a foreign key.
•
A parent row is a row in a parent table whose primary key value matches a foreign key value.
•
A delete-connected table occurs if your deletion of rows in one table causes the deletion of rows in a second table. The following conditions determine whether tables are delete-connected:
•
A self-referencing table is delete-connected to itself.
•
Dependent tables are always delete-connected to their parents, regardless of the delete rule.
•
A table is delete-connected to its grandparents when the delete rules between the parent and grandparents is CASCADE.
•
A dependent table is a table that contains one or more foreign keys. Each of these foreign keys can reference a primary key in either the same or a different table. A dependent table can contain multiple foreign keys.
Every foreign key value in a dependent table must have a matching primary key value in the associated parent table. In other words, if a foreign key contains a particular value, the primary key of one of the rows in the foreign key’s parent table must also contain that value.
Attempting to insert a row into a dependent table fails if the parent table for each referential constraint does not have a matching primary key value for the foreign key value in the dependent table row being inserted. Attempting to delete a row in a parent table to which foreign keys currently refer either fails or causes the dependent rows to be deleted as well, depending on how you have defined the referential constraints.
•
A dependent row is a row in a dependent table; its foreign key value depends on a matching primary key value in the associated parent row.
•
An orphan row is a row in a dependent table that has a foreign key value that does not exist in the index corresponding to the parent table’s primary key. The dependent key value does not have a corresponding parent key value.
•
A reference is a foreign key that refers to a primary key.
•
A reference path is a particular set of references between dependent and parent tables.
•
A descendant is a dependent table on a reference path. It may be one or more references removed from the path’s original parent table.
•
A self-referencing table is a table that is its own parent table; the table contains a foreign key that references its primary key.
•
A cycle is a reference path in which the parent table is its own descendant.