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 eight built-in facilities that ensure the data integrity in the database:
- Unique constraints and primary keys
- Foreign keys (also referred to as referential integrity)
- Check constraints in table definitions
- Check options in view definitions
- Default values
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. CHARACTER(20) or INTEGER) or through the use of domains.
A domain definition consists of a data type, optional check conditions and an optional default value. Data which falls outside the constraints defined by the check conditions will not be accepted in a column which is defined as belonging to the domain.
A column belonging to a domain for which a default value is defined (unless there is an explicit default value for the column) will automatically receive that value if row data is entered without a value being explicitly specified for the column.
Unique Constraints and Primary 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.
The DELETE and UPDATE rules defined for the referential constraint provide 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.
The following example illustrates the column CURRENCY_CODE in the table COUNTRIES as a foreign key referencing the primary key of the table CURRENCIES.CREATE TABLE countries ( code CHARACTER(2) PRIMARY KEY, country VARCHAR(48) NOT NULL, currency_code CHARACTER(3) NOT NULL, FOREIGN KEY (currency_code) REFERENCES currencies(code));
Andorra EUR United Arab Emirates AED Afghanistan AFA ... ...
UAE Dirhams Afghanis Leke ...
In this example, the referential constraint means there cannot be a currency in the COUNTRIES table that does not exist, and a currency cannot be deleted if it is assigned to a country.
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.
The exception to this rule is when foreign key relationships are defined for tables in a CREATE SCHEMA statement. Object references in a CREATE SCHEMA statement are not verified until the end of the statement, when all the objects have been created. Therefore, it is possible to reference a table that will not be created until later in the CREATE SCHEMA statement.
Check conditions may be specified in table and domain definitions to make sure that the values in a column conform to certain conditions.
Check conditions are discussed in detail in Check Constraints.
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.
For example, the restriction view USD_COUNTRIES is created with the following SQL statement:CREATE VIEW usd_countries AS SELECT code, country, currency_code FROM countries WHERE currency_code = 'USD' WITH CHECK OPTION;
This means that the view USD_COUNTRIES contains CODE, COUNTRY and CURRENCY_CODE columns from the COUNTRIES table on the condition that the value in the CURRENCY_CODE column is USD.
Any attempt to change contents of the CURRENCY_CODE column in the view or to insert data in the view where CURRENCY_CODE does not contain USD is rejected.
If check option is not used, a user could update a row in the view that is not returned by the view.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40