Referential Constraints
Databases on which you define referential constraints must meet the following requirements:
•
The database must have a database name.
•
The database must reside on a single workstation drive or a single mapped network drive.
•
The data files must be in 6.x or later transactional database engine format.
For information about converting 5.x or later data files to 6.x or 7.x format, refer to the Advanced Operations Guide.
In order for a database to support referential integrity it must also support the concept of foreign keys. A foreign key is a column or set of columns in one table (called the dependent table) that is used to reference a primary key in another table (called the parent table). The RI rule requires all foreign keys’ values to reference valid primary key values. For example, a student cannot enroll in a nonexistent course.
You can use a CREATE TABLE or ALTER TABLE statement to define keys on a table in a named database. The following sections explain how to create and modify keys. These sections also provide examples of referential constraints.
After you define referential constraints on a database, applications that do not perform data updates according to referential rules may fail. For example, if an application tries to insert a row into a dependent table before inserting the corresponding parent row into the parent table, the insertion fails. Refer to the section Referential Integrity Rules for more information.
*Note: If a file has referential constraints defined, it is a bound data file. If a user tries to access it with Btrieve, then the Btrieve user can access the file, but can only perform actions within RI constraints. For more information about bound data files, refer to the section Understanding Database Rights.
Referential Integrity Rules
Certain rules apply to inserting and updating rows in dependent tables and updating and deleting rows in parent tables when you define referential constraints on database tables. Pervasive PSQL supports the restrict and cascade rules as follows:
•
Insert into dependent table — The parent table for each foreign key definition must have a corresponding primary key value for the foreign key value being inserted. If any parent table does not have a corresponding value, then the Insert operation fails.
•
Update in the dependent table — The parent table for each foreign key definition must have a corresponding primary key value for the foreign key value (the new value for the foreign key). If any parent table does not have a corresponding value, then the Update operation fails.
•
Update in the parent table — This is not allowed. You cannot update primary key values. To perform a similar operation, delete the row you want to update, then insert the same row with the new primary key value.
•
Delete in the parent table — You can specify either the cascade or restrict rule for this operation. Cascade means that if a dependent table contains a foreign key value that matches the primary key value being deleted, then all rows containing that matching value are deleted from the dependent table also.
Restrict means that if a dependent table contains a foreign key value that matches the primary key value being deleted, then the Delete operation on the parent table fails. The cascade operation is recursive; if the dependent table has a primary key that is the parent table of a cascade foreign key, then the process is repeated for that set of data.
Insert Rule
The insert rule is a restrict rule. For each foreign key in the row being inserted, the foreign key value must be equivalent to a primary key value in the parent table. The parent table must contain a parent row for the foreign key in the row you are inserting; otherwise, the insertion fails. Pervasive PSQL causes the transactional database engine to automatically enforce the insert rule on dependent tables.
 
Update Rule
The update rule is also a restrict rule. A foreign key value must be updated to an equivalent primary key value in the parent table. If the parent table does not contain a parent row for the foreign key value, the update fails.
You can explicitly specify the update rule as restrict when you define a foreign key on a table; however, Pervasive PSQL causes the transactional database engine to enforce the rule by default if you do not specify it.
Delete Rule
You can explicitly specify the delete rule as either restrict or cascade when you define a foreign key. If you do not specify the delete rule explicitly, Pervasive PSQL assumes a default of restrict for the delete rule.
•
If you specify restrict as the delete rule, Pervasive PSQL causes the transactional database engine to check each row you attempt to delete from a parent table to see if that row is a parent row for a foreign key in another table. If it is a parent row, Pervasive PSQL returns a status code and does not delete the row. You must first delete all corresponding rows in the referenced table or tables before you can delete the parent row.
•
If you specify cascade as the delete rule, Pervasive PSQL causes the transactional database engine to check each row you attempt to delete from a parent table to see if that row is a parent row for a foreign key in another table. The transactional database engine then checks the delete rule for each descendant of that table. If any descendant has restrict as the delete rule, the attempted deletion fails. If all descendants have cascade as the delete rule, Pervasive PSQL deletes all dependent rows on the reference path to the original parent table.
The following guidelines govern the delete rule for foreign keys:
•
A cycle with two or more tables cannot be delete-connected to itself. Consequently, the delete rule for at least two of the dependent tables in the cycle must not be cascade.
•
The last delete rule in all paths from one table to another must be the same.
•
If the delete rule for the foreign key is cascade, then the table containing the foreign key may not have a delete trigger defined on it.
•
If the table containing the foreign key has a delete trigger defined on it, then the delete rule must be restrict.
Pervasive PSQL enforces these guidelines on databases that have referential constraints defined. If you attempt to declare delete rules that violate these guidelines, Pervasive PSQL returns a status code to indicate an error occurred.
Pervasive PSQL enforces the delete rule guidelines to avoid certain anomalies that might otherwise occur when you delete dependent rows from tables. Following are examples of anomalies that might occur without these guidelines.
Anomaly on Delete-Connected Cycles
A cycle with two or more tables cannot be delete-connected to itself. Consequently, the delete rule for at least two of the dependent tables in the cycle must be restrict.
Assume you want to execute the following statement.
DELETE FROM Faculty
Because of the relationships between the Faculty and Department tables, deleting a row from Faculty first deletes a row from Faculty, then from Department, where the cascaded delete stops because of the restrict rule on the name of the department.
The results could be inconsistent, depending on the order in which Pervasive PSQL deletes rows from the Faculty table. If it attempts to delete the row in which the ID is 181831941, the delete operation fails. The restrict rule on the Department name prevents Pervasive PSQL from deleting the first row in the department table in which the primary key value equals Mathematics, since the second row in Faculty continues to reference this row’s primary key.
If instead, Pervasive PSQL deletes the Faculty rows in which the primary keys equal 179321805 and 310082269 first (in either order), all the rows in Faculty and Department are deleted.
 
Since the result of the example DELETE statement is consistent, no rows are deleted.
Anomaly on Multiple Paths
Delete rules from multiple delete-connected paths must be the same. Figure 2 shows an example of one anomaly that might occur without this guideline. In the figure, the arrows point to the dependent tables.
Figure 2 Multiple Paths Anomaly
Faculty is delete-connected to Room through multiple delete-connected paths with different delete rules. Assume you want to execute the following statement.
DELETE FROM Room
WHERE Building_Name = 'Bhargava Building'
AND Number = 302;
The success of the operation depends on the order in which Pervasive PSQL accesses Faculty and Department to enforce their delete rules.
•
If it accesses Faculty first, the delete operation fails because the delete rule for the relationship between Room and Faculty is restrict.
•
If it accesses Department first, the delete operation succeeds, cascading to both Department and Faculty.
To avoid problems, Pervasive PSQL insures that the delete rules for both paths that lead to Faculty are the same.