Setting Up Referential Integrity

Referential integrity (RI) allows you to modify or prohibit updates, inserts, or deletes based on whether identical field values exist in the same or other tables.

An understanding of RI depends the concepts of rule, primary key, foreign key, cascade rule, and restrict rule. This topic gives their definitions.

A rule is a simple statement of cause and effect, carried out by the RI system defined in the database.

For example, a delete rule defines what happens to records containing a foreign key when a record containing a primary key is deleted: “When the record containing ‘Bhargava Building’ is deleted, all rows in Table A that reference that record are deleted.”

A delete rule can also prohibit the row containing the primary key value from being deleted if there are any foreign key values that reference the given primary key value.

An update rule defines what happens to a record containing a foreign key when a user attempts to update the record or add a new record: “When a user attempts to insert a new record to Table B, reject the attempt if the building name does not exist in Table C.”

A primary key is the column or columns upon which a rule depends. Only one primary key is permitted in any table, and the primary key must not allow duplicate values. For an update rule, the primary key is the column or columns against which updated or inserted columns are compared to determine if the updated or inserted record should be allowed.

In Example A, the column containing “Bhargava Building” is the primary key.

In Example B, the column in Table C that contains the building name is the primary key.

A foreign key is the column or columns that are compared against a primary key to determine how to proceed.

In Example A above, the column in Table A that may contain the value “Bhargava Building” is the foreign key.

In Example B above, the column in Table B that contains the building name is the foreign key.

A cascade rule is a rule in which the database permits the desired operation to occur, then enforces RI by changing other tables or rows to synchronize with the first operation. For example, if a delete cascade rule is defined, deleting a record in the primary key table causes the database to find and delete all rows throughout the database that have foreign key values the same as the primary key value of the deleted row.

A restrict rule is a rule in which the database decides whether or not to permit the desired operation based on existing values in the database. For example, if an update restrict rule is defined, an attempt to add a row to a table containing a foreign key causes the database engine to compare the value in the foreign key field to the values in the primary key. If there is no primary key row with the same value, the new row is not permitted to be added to the foreign key table.

This topic explores the concepts behind primary keys and foreign keys in further detail.

In the example shown above, the column named student_ID in Table A (A.student_ID) is an IDENTITY data type that does not allow two rows to the have the same value. Every student has a unique ID number. We will define student_ID as the primary key of Table A.

We can then define the column named stud_ID in Table B (B.stud_ID) as a foreign key that references A.student_ID. Note that the data type of stud_ID must be a type that can be compared with IDENTITY, such as INTEGER. The data types of primary and foreign keys must be compatible. You can have as many foreign keys as you need in order to enforce your desired referential integrity scheme. Multiple foreign keys can reference the same primary key.

Table 17 Primary and Foreign Keys
Table A	Table B
student_ID	Name	stud_ID	Class
20543	John	20543	ENG-101
20577	Mary	20543	AST-202

The table with the primary key can be referred to as the parent table, while the table with the foreign key is called the child table. Once the keys are defined, we have a range of behaviors to choose from, as shown in Table 18. You can define as many rules as fit your needs, but you can only have one of each type. For example, if you define a delete restrict rule, you cannot define a delete cascade rule on the same keys, because the two behaviors are mutually exclusive.

Table 18 Choices for RI Rules
If you want this behavior...	... define this rule:
Do not allow a row to be inserted or updated in Table B unless the proposed value of B.stud_ID matches any value in A.student_ID.	Update Restrict
Do not allow a row to be deleted from Table A if any value of B.stud_ID matches that row.	Delete Restrict
If a row is deleted from Table A, delete all rows from Table B in which B.stud_ID matches the value of A.student_ID in the deleted row.	Delete Cascade

Continuing with the example, setting an update restrict rule ensures that the value of B.stud_ID in any new or updated row must first exist in A.student_ID. It follows, then, that you must have rows in Table A before you can add any rows in Table B. Stated another way, you must create at least one parent row before you can create a child row.

In the example, setting a delete restrict rule ensures that a row from Table A cannot be deleted if any rows in Table B reference that row. You cannot delete the row with Name value “John” because John’s student ID is referenced in Table B.

Once all rows from Table B that reference John’s student ID are deleted, then John’s row can be deleted from Table A.

In the example, setting a delete cascade rule ensures that both records in Table B are deleted if the row with Name value “John” is deleted.

Zen allows a circular delete cascade on a table that references itself. Because of this, use delete cascade with caution. Ensure that you do not inadvertently delete all records in the parent table, the child table, or both.

An example helps clarify how such cascading deletion could occur. Suppose that you create the following table, d3, with two columns:

You then alter the table to add a foreign key with a delete cascade rule:

The following statement deletes all rows in the table:

Why are all rows deleted instead of just the row where c1 =2?

Delete cascade deletes any row with a foreign key equal to the primary key that is deleted. The second row has a foreign key relationship to the first row. Similarly, the third row has a foreign key relationship with the third, and the fourth row with the third. Because of the foreign key relationships, the delete cascade rule traversed all of the rows, causing the second row to be deleted because of the first, the third because of the second, and the fourth because of the third.

Zen does not allow circular delete cascade on tables that reference each other. For example, consider the following scenario in which you have tables d1 and d2:

The following alter statement is allowed:

The following alter statement is not allowed because tables d1 and d2 already have a delete cascade relationship:

You can create primary keys using SQL statements or Zen Control Center. See Columns Tasks in Zen User’s Guide.

You can create a primary key when you create a table, by using the PRIMARY KEY keywords in your CREATE TABLE statement. A primary key can consist of one or more columns. The following example shows the column named id being created then being designated the primary key:

The next example shows how to create a primary key using more than one column as the unique key value:

Regardless of whether you specify the UNIQUE attribute on the column or columns that you designate as a primary key, the database engine automatically creates an index on the designated columns that does not allow duplicate values or null values in the columns. Null values are never allowed in a key column. Every primary key value must be unique.

For more examples, see CREATE TABLE in SQL Engine Reference.

You can add a primary key to an existing table through ZenCC or by using the ALTER TABLE statement with ADD PRIMARY KEY. In Zen User’s Guide, see To set or remove a column as a primary key and SQL Editor.

You must create the primary key on a column or columns that do not allow duplicate values or null values.

If necessary, you can modify the column attributes and make the column the primary key at the same time. Here is an example using SQL:

If you want to add a primary key consisting of more than one column, you must add the key separately:

For more examples, see ALTER TABLE in SQL Engine Reference.

You can create foreign keys using SQL statements or Zen Control Center. When you create a foreign key, you may define an associated rule at the same time. You can define multiple rules on the same key. If you create a foreign key without specifying associated rules, the default referential integrity is restrict for both update and delete.

You can create a foreign key when you create a table, by using the REFERENCES keyword in your column definition. A foreign key can consist of one or more columns. The data types of the column(s) must be the same as the primary key that this foreign key references. The example next shows the column named your_id being created then being designated the foreign key, referencing mytable.id:

You can also add the foreign key designation at the end of the statement. You must use this technique if you wish to use multiple columns in the key:

When you create a foreign key, the database engine adds an index on the designated columns.

For more examples, see CREATE TABLE in SQL Engine Reference.

You can add a foreign key to an existing table with ZenCC or by using the ALTER TABLE statement with ADD FOREIGN KEY. In Zen User’s Guide, see Foreign Keys Tasks and SQL Editor.

In the following example, two rules are defined for this foreign key, both a delete rule and an update rule:

Use DELETE CASCADE with caution. See examples in Delete Cascade.

For more examples, see ALTER TABLE in SQL Engine Reference.

While Zen is designed to support concurrent access to the same data through both the Relational Engine and the MicroKernel Engine, some features of the relational (SQL) database architecture may interfere with Btrieve access to the data. For example, features that are designed to limit relational access, such as referential integrity (RI), may also limit Btrieve access in the interest of preserving data integrity.

You should fully understand integrity enforcement, bound databases, ODBC/SQL security, triggers, referential integrity and owner names before implementing these features on a database that is used by a transactional (Btrieve) application. In most cases you can get “best of both worlds” access to your database, but since security, referential integrity, and triggers can put constraints on access or operations, some Btrieve operations may be restricted or prevented depending on the implementation.

In some cases using integrity enforcement, bound databases, security, triggers, or referential integrity restrict Btrieve access to the data or file or prevent it completely when Btrieve access would violate restrictions placed on that data. Triggers and RI mainly limit the ability to manipulate data through the Btrieve API.

Security and owner names can limit access and/or the ability to manipulate that data without the proper account, rights, and password. There are many possible combinations of these features, so only the most common ones are listed here.

Table 19 Interactions Between Relational Restrictions and Btrieve Access
CONDITIONS						RESULTS
DDFs Exist?	Integrity Enforced?	Bound Database?	Relational Security Used?	Triggers Used?	RI Used?	Btrieve Access allowed?	SQL/ODBC Access Allowed?
No	-	-	-	-	-	Yes	No
Yes	No	No	No	No	-	Yes	Yes
Yes	No	No (1)	No	Yes (2)	-	Yes (2)	Yes
Yes	Yes	No	No	No	No	Yes	Yes
Yes	Yes	No	Yes	No	No	Yes (3)	Yes (3)
Yes	Yes	No (1)	Yes	No	Yes	Yes (4)	Yes (3) (4)
Yes	Yes	No (1)	Yes	Yes (2)	No	Yes (1)	Yes (3)
Yes	Yes	Yes	No	No	No	Yes	Yes
Yes	Yes	Yes	Yes	No	No	Yes (3)	Yes (3)
Yes	Yes	Yes (1)	Yes	No	Yes	Yes (2) (4)	Yes (3) (4)
Yes	Yes	Yes (1)	Yes	Yes (2)	No	Yes (2) (3)	Yes (3)

(1) Regardless of the Bound Database setting for a database, the database engine automatically stamps a data file as bound if it has a trigger, a foreign key, or a primary key that is referenced by a foreign key. For more information on the meaning of a bound database or file, see Bound Databases.

(2) Adding triggers on a table prevents access to that file from the Btrieve API, for any operations that would cause the trigger to execute. Because triggers do not react to database operations coming through the Btrieve interface, this lock-out behavior preserves the consistency of the data. See Bound Database versus Integrity Enforced for more information.

(3) When a database or file is secured, access is allowed as long as the user has permissions (that is, a relational user name and password or a valid Btrieve owner name) to that file. Files that are in a secure database but do not have Btrieve owner names set are accessible to Btrieve users. When relational security is first set up for a file that already has a Btrieve owner name, the Master user must grant relational permissions to users using the Btrieve file owner name. See Zen Security for more information.

(4) If a table contains referential integrity constraints, and Integrity Enforced is turned on for the given database, both Btrieve and SQL operations that would violate the constraints are disallowed. This mechanism preserves the integrity of the data regardless of the method of access.

If you do not specify the Integrity Enforced attribute for a named database, the database engine does not enforce any referential integrity, triggers, or security rules. If you specify the Integrity Enforced property for a named database, the MicroKernel enforces the defined security, RI, and triggers regardless of the method you use to access the data. The MicroKernel enforces these rules as follows:

•Btrieve users are not subject to relational security. If you have owner names on the files, they remain in effect. If you do not have owner names on the files, any Btrieve user can access the data regardless of relational security constraints. Btrieve operations are subject to all the RI and other constraints defined in the database as well as the Trigger restrictions listed below.

Table 20 Constraints on Btrieve Access - Integrity Enforced
Constraint on File	Level of Access Permitted Using Btrieve
RI constraints defined	User can access the data and perform any operations within RI constraints.
INSERT triggers defined	Read-only, update, and delete operations permitted.
UPDATE triggers defined	Read-only, insert, and delete operations permitted.
DELETE triggers defined	Read-only, update, and insert operations permitted.

If more than one constraint exists on the bound file, the access level follows the most restrictive constraint or combination of constraints. For example, if a file has both INSERT and UPDATE triggers defined, then Btrieve users have only read-only and delete access.

The Integrity Enforced setting is not directly related to the Bound Database setting. A database can be bound without enforced integrity, or a database can have integrity enforced without being bound.

If you specify the Bound attribute for a named database, the DDFs and data files in that database cannot be associated with any other database. Also, a bound data file cannot be associated with more than one table definition in the database. When you add new tables or DDFs to a bound database, the database engine automatically binds the new objects. This behavior prevents conflicts that could cause unpredictable behavior or data integrity corruption. For example, if you used the same data file for two different table definitions in the same database, you could define RI rules on one table but not on the other. In this case, inserting a row into the table without the RI rules would violate the RI rules on the other table. Binding the data files and DDFs prevents such conflicts.

DDFs and data files can be individually bound. The database engine automatically marks a data file as bound if it has a trigger, has a foreign key, or has a primary key that is referenced by a foreign key. These files cannot be shared with another database or associated with more than one table definition.

Whether a data file is bound has no direct affect on Btrieve access to that data file. However, files that are bound often have other constraints that may limit Btrieve access.