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 four built-in facilities that ensure the data integrity in the database:
- Foreign keys (also referred to as referential integrity)
- Check statements in table definitions
- Check options in view definitions
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.
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 and length may be specified explicitly, e.g.
INTEGER(5), 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 and length specification with optional check conditions and a 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.
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.
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
USAGErights on the domain, see the Mimer SQL User's Manual, Granting Privileges.
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 another 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.
DELETErule 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.
Note: The referential integrity constraints are effectively checked at the end of an INSERT, DELETE or UPDATE statement.
Foreign key relationships are defined when a table is created using the
CREATE TABLEstatement and can be added to an existing table by using the
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 SCHEMAstatement, it is possible to reference a table that will not be created until later in the
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.
Check conditions may be specified in table and domain definitions to make sure that the values in a column 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.