In computer science , the term integrity condition denotes conditions that are placed on the state of a process or a data structure. With respect to databases , states are said to be consistent if they meet the constraints.
Integrity constraints describe assumptions that are made about the data or the state, for example a certain data type, a range of values or a dependency relationship between two objects. Based on these assumptions, a programmer can describe processing processes and, if necessary, change the status of a process. Compliance with the integrity constraints should not be left to the programmer, but rather checked by the system. Inconsistent states (that is, states that violate the integrity constraints) or imprecisely defined integrity constraints are common causes of programming errors , as the individual subroutines / functions rely on their compliance.
Relational database systems offer the possibility of formulating integrity conditions when defining a relational schema, compliance with which is guaranteed by the system. A typical example of constraints are key and foreign key relationships .
It can be specified how compliance should be guaranteed or how changes should be reacted to. A change that violates an integrity condition can either be prevented entirely or further changes to restore the integrity can be made (see: Database trigger ). The following SQL example models a connection between professors , students , lectures and exams in a very simplified way . The focus here should be on the foreign key relationships:
create table Professor ( ID integer primary key ); create table Student ( MatrNr varchar(16) primary key ); create table Vorlesung ( ID integer primary key, Name varchar(32), Prof integer references Professor.ID on delete set null ); create table Prüfung ( Datum date, Vorlesung integer not null references Vorlesung.ID on delete no action, Stud varchar(16) not null references Student.MatrNr on delete cascade );
The following constraints are defined in this example:
- A lecture references a professor. If the professor retires (and the corresponding data record is deleted), the lecture will remain. The addition on delete set null deletes the reference to the professor if the referenced data record is deleted (integrity condition: the referenced professor holds the lecture).
- Each exam references a lecture. As long as there is still an examination for a lecture, this lecture may not be deleted from the database. The addition on delete no action prevents a referenced data record from being deleted from the "lecture" table. (Integrity condition: there is also a lecture for each examination).
- Each exam references a student who is taking the exam. If the student is de-registered (and the relevant data record is deleted), the examination will not take place. The addition on delete cascade causes an exam to be deleted if the referenced student is deleted. (Integrity condition: without a student there is no exam)
The database ensures compliance with this condition.
Naturally, the specification of integrity constraints restricts the number of operations allowed. Since these restrictions can also affect other tables in a relational database than the specific table within which the condition was specified, there is a special authorization in some databases that allows a table that has been created to be referenced. In the above example, the addition on delete no action prevents the deletion of entries in the lecture table . Accordingly, the owner of the table check must have the authorization to reference the table lecture .
Since compliance with integrity conditions within the database can result in complex checks, in order to improve the runtimes, in most cases, their explicit specification is dispensed with. The consequence of this is data inconsistencies within the database. Depending on the application scenario, the associated software must be able to recognize and take into account the remaining inconsistent data within the database. If compliance with the conditions is not guaranteed by the application itself and the resulting data inconsistency cannot be circumvented, the software will not run error-free.
A variety of integrity constraints can also be found in the context of word processing or document processing. It is intuitively obvious that the integrity of a document is violated if the table of contents contains incorrect page numbers or hyperlinks in HTML pages point to nothing.
In a formalized form, integrity constraints for XML documents can be described, for example, using an XML schema . The following XML element definition could, for example, be used to define a new element type that only allows information on the body temperature of people. In this case 3 decimal places, 1 decimal place as well as minimum and maximum values
<simpleType name="celsiusKörperTemp"> <restriction base="xsd:decimal"> <totalDigits value="3"/> <fractionDigits value="1"/> <minInclusive value="30.0"/> <maxInclusive value="42.5"/> </restriction> </simpleType>
- Alfons Kemper, André Eickler: Database systems. An introduction. 5th, updated and expanded edition. Oldenbourg, Munich et al. 2004, ISBN 3-486-27392-2 .
- Victor M. Markowitz: Safe Referential Structures in Relational Databases. In: Guy M. Lohman, Amílcar Sernadas, Rafael Camps (eds.): Very large Data Bases. Proceedings of the Seventeenth International Conference on Very Large Data Bases. September 3-6, 1991, Barcelona (Catalonia, Spain). Kaufmann, San Mateo CA et al. 1991, ISBN 1-55860-150-3 , pp. 123-132.