An integrity constraint is a rule which restricts the possible range of values for a column or for several columns. In much the same way as only those values are added to the database which are compatible with the data type defined for the corresponding column, SESAM/SQL only permits values which fulfil the defined integrity constraints. An integrity constraint can be seen as a search condition formulated for one column or for several columns and for which the truth value must never be false. A row can only be added to or deleted from a table and a column value can only be changed if all the relevant integrity constraints continue to be fulfilled after the change is made.
An integrity constraint can be defined during definition of the table with CREATE TABLE. The ALTER TABLE statement allows an integrity constraint to be added to or deleted from an existing base table.
An integrity constraint can be specified as a table constraint or a column constraint. A table constraint is an integrity constraint which is declared for one column or a combination of columns. If the integrity constraint references one column only, it can be specified as a column constraint during definition of the column concerned.
An integrity constraint has a name which can be assigned explicitly when the integrity constraint is defined or which is assigned implicitly by SESAM/SQL. If an integrity constraint is not fulfilled, a message which references this name is issued.
When a user defines a new integrity constraint, SESAM/SQL checks whether the constraint is fulfilled by the existing data in the database. If this is not so, the definition of the integrity constraint is rejected. If the table is empty, the integrity constraint is always true.
SESAM/SQL distinguishes the following integrity constraints:
NOT NULL constraint
The NOT NULL constraint requires that a column contain no NULL values. The NOT NULL constraint can only be specified as a column constraint.
UNIQUE constraint
The UNIQUE constraint for a single column requires that any value other than null must occur once only in the specified column. The UNIQUE constraint for a combination of columns requires that any combination of values which does not contain NULL must occur only once in the specified combination of columns.
PRIMARY KEY constraint
The PRIMARY KEY constraint defines a column or set of columns as the primary key of a table. The PRIMARY KEY constraint requires that the column or set of columns satisfy the UNIQUE and NOT NULL constraints.
A table can only have one primary key. The primary key must not be of the data type VARCHAR or NVARCHAR.
It is only possible to define a primary key constraint for CALL DML tables.
Referential constraint
A referential constraint ([FOREIGN KEY]...REFERENCES) defines a column or a combination of columns as a foreign key for a table. The foreign key references one or more columns in another table. A UNIQUE constraint must have been declared for these columns. The table containing the foreign key is known as the referencing table and the table for whose columns the UNIQUE constraint must have been declared is known as the referenced table. The number and data types of the associated columns must be identical in the referencing and referenced tables. The same base table can be taken for the referencing table and the referenced table.
A row in the referencing table fulfils the referential constraint either if one of the referencing columns contains the null value or if all the values in the referencing columns are non-null and there is a row in the referenced table whose referenced columns contain identical values. If the referential constraint is not fulfilled when a row is inserted in the referencing table or the referencing columns are updated, or when rows in the referenced table are updated or deleted, SESAM/SQL rejects the relevant table operations.
In the case of single-column foreign keys, the referential constraint requires that every value other than NULL for the foreign key of a table occurs as the value of a particular column in a different table where a UNIQUE constraint is fulfilled.
In the case of multiple-column foreign keys, every combination of values which occurs and which does not include a null value must occur in the corresponding columns in the referenced table. This combination of columns must fulfil a UNIQUE constraint. Thus, in SQL, a row fulfils the referential constraint if it contains a null value in at least one column of a multiple-column foreign key.
If no column(s) are specified for the referenced table after REFERENCES, the primary key of the referenced table is used.
Check constraint
The check constraint requires that every column value or every combination of column values fulfils a search condition. The search condition may only reference the table to which the column(s) belong and must not contain a subquery or transliteration between EBCDIC and Unicode. In addition, the search condition may not contain conversion of uppercase letters to lowercase letters or lowercase letters to uppercase letters if the string to be converted is a Unicode string. Since integrity constraints must not be dependent on a particular application, you are not allowed to specify a host variable, a time function or a special literal in the search condition. The search condition may not reference a multiple column.
Deleting integrity constraints
A primary key constraint can only be deleted by deleting the table concerned with the DROP TABLE statement. The other integrity constraints can be deleted with the ALTER TABLE DROP CONSTRAINT statement or are deleted implicitly with DROP TABLE.
A UNIQUE constraint can only be deleted with ALTER TABLE DROP CONSTRAINT RESTRICT if it does not apply to the referenced columns of a referential constraint.