Data Integrity

---

The following facilities are available for ensuring the integrity of a Mimer SQL database:

• domains
• entity integrity (non-NULL primary keys)
• referential integrity (foreign keys)
• table integrity
• view integrity

Domains

Domains define sets of permissible values. By assigning a table column to a domain when the table is created or altered, the values which the column may contain are restricted to those defined in the domain. Any number of columns may use a given domain.

The ident defining a table column must hold USAGE privilege on any domain used.

A default value may also be defined for a domain. The domain default value is inserted into a column defined using the domain when data is inserted without an explicit column value being specified.

If the default value for the domain is defined outside the range of restriction values for the column, attempts to store the default value in a column using the domain will fail. In such a case an explicit value must always be specified when inserting data into the column.

The use of domains in table definitions is recommended, since this can provide an automatic check on the validity of data inserted into the column. However, domain definitions should be carefully planned, since a domain definition cannot currently be altered after it has been defined.

Entity Integrity

Entity integrity refers to the requirement that every row in a table must be uniquely identified and that no row in a table may be identified by NULL (i.e. by an unknown value).

Entity integrity can only be enforced if a primary key constraint or unique constraints are applied.

All primary key columns in tables created by Mimer SQL are defined as NOT NULL, thus ensuring entity integrity. Other (i.e. non-primary key) columns may also be defined as NOT NULL as required.

Referential Integrity

Referential integrity refers to the requirement that data entered into a table in the database must already be present in another table (e.g. a component may not be entered in a parts list if it does not already exist in the set of known components in the database).

Mimer SQL supports referential integrity through the FOREIGN KEY clause in the CREATE TABLE statement.

The properties of a FOREIGN KEY are as follows:

• The columns defined as a foreign key must correspond exactly in number and data type to the primary key or unique key columns in the referenced table.
• Data inserted into the foreign key columns (by either INSERT or UPDATE operations) must either already be present in the primary key or a unique key of the reference table or include at least one NULL column.
• A primary or unique key value that is referenced by a foreign key must not be removed by an update operation. It may be possible to remove such a value with a delete operation provided the ON DELETE rule is used to update the referencing table in a way that preserves the referential integrity.

Note: Referential integrity constraints are effectively checked at the end of the INSERT, DELETE or UPDATE statement. Both the table containing the foreign key reference and the referenced table must be stored in a databank with either the TRANS or LOG option.

Table Integrity

Table integrity refers to the facility in Mimer SQL of defining CHECK clauses in a table definition, whereby the contents of one column is checked against the contents of one or more other columns in the same row of the table.

Data may only be entered into the table if the CHECK constraint is not violated.

View Integrity

View integrity refers to the facility in Mimer SQL of including a WITH CHECK OPTION clause in a view definition. If a view is defined with a WITH CHECK OPTION, data which violates the definition of the view may not be entered into the view by INSERT or UPDATE operations.

When a view is defined with a CHECK OPTION, any views defined on that view will inherit the CHECK OPTION.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se