Zen v16 | Setting Up Referential Integrity

Advanced Reference > Advanced Operations Guide > Setting Up Referential Integrity

Setting Up Referential Integrity

Referential integrity is a system of checks and balances that you can create in your database to ensure that tables with related data remain synchronized.

• Concepts of Referential Integrity

• Setting up Primary Keys

• Setting up Foreign Keys

• Interactions Between Btrieve and Relational Constraints

Concepts of 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.

Definitions

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

Rule

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

Example A

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.

Example B

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."

Primary key

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.

Foreign 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.

Cascade Rule

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.

Restrict Rule

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.

Understanding Keys and Rules

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

Table A		Table B
student_ID	Name	stud_ID	Class
20543	John	20543	ENG-101
20577	Mary	20543	AST-202

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.

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 the following table. 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.

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

Update Restrict

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.

Delete Restrict

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.

Delete Cascade

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:

CREATE TABLE d3 (c1 INT PRIMARY KEY, c2 INT)

INSERT INTO d3 VALUES (2,2)

INSERT INTO d3 VALUES (3,2)

INSERT INTO d3 VALUES (1,3)

INSERT INTO d3 VALUES (4,1)

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

ALTER TABLE d3 ADD FOREIGN KEY (c2) REFERENCES d3 ON DELETE CASCASE

The following statement deletes all rows in the table:

DELETE FROM d3 WHERE c1 = 2

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:

CREATE TABLE d1 (c1 INT PRIMARY KEY, c2 INT)

CREATE TABLE d2 (e1 INT PRIMARY KEY, e2 INT)

The following alter statement is allowed:

ALTER TABLE d1 ADD FOREIGN KEY (c2) REFERENCES d2 ON DELETE CASCADE

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

ALTER TABLE d2 ADD FOREIGN KEY (e2) REFERENCES d1 ON DELETE CASCADE

Setting up Primary Keys

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

Creating a Primary Key During Table Creation

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:

CREATE TABLE mytable (id INTEGER,

myname CHAR(20),

PRIMARY KEY(id))

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

CREATE TABLE mytable (id INTEGER,

myname CHAR(20),

PRIMARY KEY(id, myname))

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.

Adding a Primary Key to an Existing Table

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:

ALTER TABLE mytable MODIFY id INTEGER UNIQUE NOT NULL PRIMARY KEY

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

ALTER TABLE mytable ADD PRIMARY KEY(id, myname)

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

Setting up Foreign Keys

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.

Creating a Foreign Key During Table Creation

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:

CREATE TABLE yourtable (your_id INTEGER REFERENCES mytable(id) ON DELETE CASCADE, yourname CHAR(20))

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:

CREATE TABLE yourtable (your_id INTEGER,

yourname CHAR(20),

FOREIGN KEY(your_id, yourname) REFERENCES

mytable(id, myname) ON DELETE CASCADE)

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.

Adding a Foreign Key to an Existing Table

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:

ALTER TABLE yourtable ADD FOREIGN KEY (your_id,yourname) REFERENCES mytable(id,myname) ON DELETE CASCADE ON UPDATE RESTRICT

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

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

Interactions Between Btrieve and Relational Constraints

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.

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 will cause the trigger to execute when the corresponding Btrieve operation is performed. A trigger can validate data, do additional work, or cause the Btrieve operation to abort. 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.

Bound Database versus Integrity Enforced

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.

• If constraints exist on a given file, Btrieve access is permitted as follows:

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, UPDATE, or DELETE triggers defined	Corresponding Btrieve operations will cause triggers to execute.

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.

Bound Databases

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.