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:
- Primary keys and unique keys
- Foreign keys (also referred to as referential integrity)
- Domains and user-defined types
- Check constraints 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.
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 NULLs in different ways. However, 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 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.
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.
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.
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 or user-defined type, the ident must first have been granted
USAGErights on it, see the Mimer SQL User's Manual, Granting Privileges.
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
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40