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. CHARACTER(20) or 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, a length specification, optional check conditions and a 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.
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 privilege on the domain, see Granting Object 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.
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 HOTELCODE in table ROOMS as a foreign key referencing the primary key of table HOTEL.
In this example, the referential constraint means there cannot be a room in a hotel that does not exist, and a hotel cannot be deleted if it has any rooms.
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. For example, the check condition in the definition of the BOOK_GUEST table, see BOOK_GUEST Table, specifies that a guest must be booked to arrive before they depart, and to checkout no earlier than they check in.
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 HOTEL_STOCKHOLM is created with the following SQL statement:CREATE VIEW HOTEL_STOCKHOLM AS SELECT NAME, CITY FROM HOTEL WHERE CITY = 'STOCKHOLM' WITH CHECK OPTION;
This means that the view HOTEL_STOCKHOLM contains NAME and CITY columns from the HOTEL table on the condition that the value in the CITY column is STOCKHOLM.
Any attempts to change contents of the CITY column in the view or to insert data in the view where CITY does not contain STOCKHOLM is rejected.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40