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.

Constraints

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 Create Table statement, or via an Alter Table 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.

Create Table F3 (
    id int identity(1, 1) not null,
    a int not null, 
    b varchar(20) not null,
    c varchar(20) not null,
    Constraint F3_pk Primary Key (id),
    Constraint F3_uc Unique (a, b)
)
go
Create Table F4 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F4_pk Primary Key (i),
    Constraint F4_fk1 Foreign Key (a, b) References F3 (a, b)
)
go

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

Create Table F5 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F5_pk Primary Key (i),
    Constraint F5_fk1 Foreign Key (a, b) References F3 (a, b)
        On Update Cascade On Delete Set Null
)
go

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.

Create Table Singleton (
    ID Char(1) Not Null,
    a varchar(20),
    ...
    Constraint Singleton_PK Primary Key (ID),
    Constraint Singleton_One_Row check(ID = 'A')
)
go

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 Begin Transaction statement. Subsequent statements will not be committed to the database until a Commit or Commit Transaction statement is issued. If a Rollback or Rollback Transaction statement is issued, all statements since the transaction began will fail, i.e. none of them will be committed to the database.

Begin Transaction
go
Insert Into U1 (a, b, c) Values (1, 'First', 'Row')
go
Rollback Transaction
go
Select * From U1
go

(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 PagesLockedToTableLock found under the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

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