JET Database/Data integrity

JET (along with its major application wrapper, Microsoft Access) is often blamed for having poor data integrity, but this is most often because few or none of the data integrity features of the database have been properly used.

The JET database supports many of the standard data integrity functions that are expected of relational databases, including constraints, transactions, and locking. This support is something that has evolved over time, with only JET 4.0 supporting some features.

There are also some locking and buffering problems associated with using JET databases in multi-user environments, and especially so over local area networks (LAN). Such problems may be the subject of another chapter, however.

Primary keys
Primary keys help to define a set of columns for a table, that can be used to uniquely identify each row. No two rows can have the same values in the columns that make up the primary key, a constraint that the database enforces by refusing to insert a row when there already exists a row with those values in the primary key columns.

Unique indexes and unique constraints
JET supports both unique indexes and unique constraints, subtly different concepts that achieve essentially the same functionality. A unique index is an index that cannot have duplicate values for the columns in the index, whereas a unique constraint is a data integrity rule that prevents rows being inserted with the same values in the columns listed in the constraint. Both can can used to implement the logical data model concept of an alternate key.

The JET engine implements the concept of the unique constraint by creating a unique index. A unique constraint can be added to a table in the  statement, or via an   statement.

Foreign keys
JET supports the foreign key constraint, allowing entity-relationship data modelling rules to be enforced at the database level. A foreign key constraint prevents rows from being inserted when no corresponding row exists in a related table, and also prevents rows from being deleted when related tables have dependent rows referencing them.

The JET engine automatically creates an index on the columns that compose the foreign key.

A foreign key constraint must reference all columns in a primary key, unique constraint, or unique index on the referenced table. The code below shows a foreign key referencing a unique constraint (e.g. an alternate key) on another table.

JET 4.0 introduced cascading updates and deletes to foreign keys. When a foreign key is created with, the foreign keys are updated if the referenced columns are changed. causes the referencing rows to be deleted if the referenced row is deleted, and  sets the foreign keys to Null if the referenced row is deleted.

Check constraints
JET 4.0 introduced check constraints, which apply additional logic in the data integrity of the database. A check constraint is an expression that further constrains the allowable values in a column. The expression can be a simple value bounding validation, or it can include a sub-query that references values in other tables.

Check constraints can be useful for more than just validating input values. The following example shows how a check constraint can ensure that a table contains only one row.

Transactions
From JET 4.0, JET supports transactions for multiple statements, giving developers the ability to write robust code that updates the database without compromising logical consistency by, for example, allowing half an invoice to be created, or half a client's records to be updated. Thus, statements within a declared transaction will succeed or fail together.

A transaction must be explicitly created by issuing the  statement. Subsequent statements will not be committed to the database until a  or   statement is issued. If a  or   statement is issued, all statements since the transaction began will fail, i.e. none of them will be committed to the database.

(0 row(s) returned)

When using Microsoft's ADO database components, transactions are typically managed through the BeginTrans / CommitTrans / RollbackTrans methods on those objects. However, they can just as easily be implemented through statement execution, as shown above.

Locking
JET supports read and write locks on the database, either through exclusive access, or shared access. Locks can be set at the row level, page level, or database level.

From JET 4.0, Row level locks will be automatically promoted to page or table level when the number of rows locked reaches a threshold. This threshold is set in the Windows registry entry  found under the key

Locking can be configured by setting the isolation level on the database connection. For ADO, this is done with the  property on the   object.