Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

Data Integrity

A vital aspect of a Mimer SQL database is data integrity. Data integrity means that the data in the database is complete and consistent both at its creation and at all times during use.

Mimer SQL has built-in facilities to ensure the data integrity in the database:

These features should be used whenever possible to protect the integrity of the database, guaranteeing that incorrect or inconsistent data is not entered into it. By applying data integrity constraints through the database management system, the responsibility of ensuring the data integrity of the database is moved from the users of the database to the database designer.

Primary Keys and Unique Keys

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns. A table cannot contain two rows with the same primary key value. If the primary key contains more than one column, the key value is the combined value of all the columns in the key. Individual columns in the key may contain duplicate values as long as the whole key value is unique.

Apart from a primary key constraint its also possible to add one or more unique constraints. The primary key constraint and the unique constraint are similar, but treat null values in different ways. A null value can never be stored in a primary key column, but a unique constraint column can contain null values.

The definition of the primary key is also a definition of the most effective access path for the table.

Foreign Keys - Referential Integrity

A foreign key is one or more columns in a table defined as cross-referencing the primary key or a unique key of a table.

Data entered into the foreign key must either exist in the key that it cross-references or be null. This maintains referential integrity in the database, ensuring that a table can only contain data that already exists in the selected key of the referenced table.

As a consequence of this, a key value that is cross-referenced by a foreign key of another table must not be removed from the table to which it belongs by an update or delete operation if this ultimately violates the referential constraint.

The DELETE rule defined for the referential constraint provides a mechanism for adjusting the values in a foreign key in a way that may permit a cross-referenced key value to effectively be removed.

Similarly, the UPDATE rule defined for the referential constraint provides a mechanism for adjusting the values in a foreign key in a way that may permit a cross-referenced key value to effectively be updated.

Note: The referential integrity constraints are effectively checked at the end of an INSERT, DELETE or UPDATE statement, or at COMMIT depending on whether the constraint is declared as IMMEDIATE or DEFERRED.

Foreign key relationships are defined when a table is created using the CREATE TABLE statement and can be added to an existing table by using the ALTER TABLE statement.

The cross-referenced table must exist prior to the declaration of foreign keys on that table, unless the cross-referenced and referencing tables are the same.

If foreign key relationships are defined for tables in a CREATE SCHEMA statement, it is possible to reference a table that will not be created until later in the CREATE SCHEMA statement.

Note: Both the table containing the foreign key and the cross-referenced table must be stored in a databank with either the TRANSACTION or LOG option.


Each column in a table holds data of a single data type and length, specified when the column is created or altered. The data type may be specified explicitly, e.g. CHARACTER(20) or INTEGER, or through the use of domains, which can give more precise control over the data that will be accepted in the column.

A domain definition consists of a data type with optional check conditions and an optional default value. Data which falls outside the constraints defined by the check conditions is not accepted in a column which is defined using the domain. If a variable or parameter in a stored routine is defined as a domain with a check constraint, it is not possible to assign the parameter or variable a value which is allowed by the check constraint.

A column defined using a domain for which a default value is defined will automatically receive that value if row data is entered without a value being explicitly specified.

A variable in a stored routine or trigger declared using a domain for which a default value is defined will automatically receive that value unless an explicit default clause is present in the declaration.

In order for an ident to create a table containing columns whose data type is defined through the use of a domain, the ident must first have been granted USAGE rights on it, see the Mimer SQL User's Manual, Granting Privileges.

Check Constraints

Check constraints may be specified in table and domain definitions to make sure that the values in a table row conform to certain conditions. See the Mimer SQL User's Manual, Check Constraints for more information.

Check Options in View Definitions

You can maintain view integrity by including a check option in the view definition. This causes data entered through the view to be checked against the view definition. If the data conflicts with the conditions in the view definition, it is rejected.

Mimer Information Technology AB
Phone: +46 18 780 92 00
Mimer SQL Documentation TOC PREV NEXT INDEX