Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Integrity


The Mimer SQL Server allows you to enforce constraints in the database, either for database integrity purposes or business-related rules. Through the use of declarative integrity constraints, database procedures, and database triggers, Mimer SQL provides a high degree of security and business rule enforcement.

Constraints define rules that enforce data integrity. Constraints are relatively simple to maintain but aren't suitable in situations where you need to enforce complex logic. With stored procedures, functions and database triggers you can enforce complex business rules at the server level, improving application performance, scalability and security, and reduce development costs. Database triggers are executed automatically when data manipulation statements are actioned, and can be used to enforce complex integrity rules in the server.

Data integrity is vital in a database; if the quality of the data is questionable, any information derived from that data must be suspect. The relational model defines four types of integrity that can be used to ensure that your data is consistent and correct:

Domain integrity is to do with the values that may be contained within a specific column. All columns have an implicit domain derived from their data type but Mimer SQL also supports the CREATE DOMAIN statement. A domain can be defined with a number of CHECK clauses and a DEFAULT value.

A domain definition can be used instead of a data type in a column definition. This has the advantage that the same definition of data type, check clauses and default value can be used in many column definitions and therefore those columns are guaranteed to have the same attributes.

If a DEFAULT value is not specified for a column definition (either explicitly or implicitly through the use of a DOMAIN) then NULL is used.

A column definition can specify NOT NULL. This indicates that the table column must contain a value for each row. By implication a column definition that does not specify NOT NULL do not have to contain an actual value. NULL is a condition (rather than a value) that represents at least one of three states of the data values: not applicable, applicable but not available, or applicability unknown. While this is not formally a domain constraint it is an important concept that is referred to in the other constraints.

Entity integrity ensures that each row in a table is uniquely identified. The concept is basic to database design. The primary key of a table is one or more columns that are used to uniquely identify each row of the table.

The primary key enforces entity integrity. A table can only have one primary key. The primary key constraint does not allow duplicate values and does not allow NULL.

Referential integrity is concerned with the maintenance of relationships between tables. A referential constraint defines a foreign key relationship between the referencing table and another table in the database (the 'referenced table'). The foreign key is defined in the referencing table as a relationship either the primary key or one of the unique keys in the referenced table.

For example, you cannot insert into the referencing table a foreign key value that does not exist in the referenced table. This rule of referential integrity ensures that a non-NULL value of a foreign key must be within the domain of the related primary or unique key.

Rules can be defines in references that specify the action to be taken on the affected rows of the referencing table when a DELETE operation is performed on the referenced table. These rules can define one of the following triggered actions: CASCADE, the affected rows are also deleted; SET NULL, the appropriate foreign key columns are set to NULL; SET DEFAULT, the appropriate foreign key columns are set to their default value; and NO ACTION, which raises an error because the referential constraint would be violated.

User integrity covers all other forms of integrity constraints that are not covered by the other three. In Mimer SQL, table integrity refers to the facility of defining CHECK clauses in table definitions whereby the contents of a column is verified against a list of acceptable values. Alternatively, it can check the relationship between values in more than one column in a row. In addition, UNIQUE constraints (alternative keys) can be defined on a table. Like the primary key, a unique key is composed of one or more table columns and a key value uniquely identifies a row in the table (i.e. there are no duplicates). View integrity means that if a view is created WITH CHECK OPTION this indicates that any data inserted into the view will be checked for conformity with the view-defining conditions.

User integrity also covers user-defined business rules, regulations, policies and procedures. Stored routines and triggers are usually used to enforce business integrity.

Stored routines (procedures and functions) can be used so that users, who require access to certain tables or views from an application, can gain access without having any explicit access rights to the actual objects. Instead the users can be granted the right to execute a stored routine (with or without GRANT option) that accesses those objects. The user creating a stored routine must, of course, have sufficient access rights to all objects involved in the routine. Using stored routines in this way allows you to create an environment where the users are forced to only perform database operations through a series of well-debugged routines.

A trigger is a special type of stored procedure that is called automatically whenever a specific operation is performed on a table. Triggers are most useful when the features supported by constraints cannot meet the functional needs of the application.

The View mechanism is a very powerful concept. It is the result set from a predefined SELECT statement, a so-called derived table. A view may simply be a restriction of a single table, or may involve the joining of two or more tables (a so called 'join view'). A view can be used anywhere where a table may be used. Views are a powerful tool for restricting user access to defined parts of the database, and complement the system of access privileges in maintaining database security. By defining restriction views (i.e. views based on one table but including only specified rows and/or columns in the table), access privileges may be granted to subsets of table contents without affecting the physical database structure. Join views can be used to create the 'Universal Relations' which are used by many applications, from a normalized database.

Views with an implicit one-to-one relationship with an underlying table are directly updateable, provided the appropriate access privileges are held. An INSTEAD OF trigger provides full view updating. The trigger body contains SQL procedural code, allowing the developer to define the operations to be performed on the tables that under-pin the view.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX