Integrity Constraint
These are constraints on the integrity of the data.
The reason for this is that a relational signature captures only the structure of relations. However, Valid database instances satisfy additional integrity constraints in the form of sentences over the signature. Some examples:
- Values of a particular attribute belong to a prescribed data type
- Values of attributes are unique among tuples in a relation (keys)
- Values appearing in one relation must also appear in another relation (referential integrity or foreign keys).
- Values cannot appear simultaneously in certain relations (disjointness)
- Values in a relation must appear in at least one of another set of relations (coverage)
Integrity Constraints in SQL
Syntax:
CREATE ASSERTION <assertion-name>
CHECK (<condition>)No pair of publications have the same pubid:
CREATE assertion unique-pubid
check (
NOT EXISTS (
SELECT * FROM publication p1, publication p2
WHERE p1.pubid = p2.pubid
AND p1.title != p2.title ) )