Relational Integrity Constraints. Rohit Khokher. Relational Integrity Constraints. • A relational database schema (i.e. DB definition) consists of – relation schemas (table definitions) – integrity constraints • Integrity constraints are rules which all instances
• A relational database schema (i.e. DB definition)
– relation schemas (table definitions)
– integrity constraints
• Integrity constraints are rules which all instances
of the DB must satisfy in order to correctly model
the real world.
• Any operation that would violate a declared
constraint will be disallowed
• Many real-world constraints are imposed by
a combination of
– good design of relations (such as via ER modelling)
– maintenance of key (uniqueness) constraints
An employee has
only one name, is in
one department, has
It is impossible to violate the rules so long as no two tuples in the employee relation have the same ID.
• specify attributes or combinations of attributes which must be unique.
• Primary keys must be unique to allow the key to be use to identify tuples.
– e.g. Employee ID must be unique in the
• we may wish to specify that other (non primary key) attributes should be unique
• ENTITY INTEGRITY: no part of a primary key field can contain NULL
– if it did it would not be distinguishable from any other NULL.
• Total participation constraints are encoded by insisting that the foreign key representing the relationship is NOT NULL
– E.g. Student Course can not be NULL
• We may wish to specify that other attributes are required (i.e. not null).
– e.g. every order must have a date associated with it.
• In Access use “Required” property for such fields.
• Aka Foreign Key constraints;
• require that a value referred to in some attribute actually exists as an entity in some table.
– The employee in a Works-On tuple, unless it is
null, must exist as an employee ID in the Employee table
• Most referential integrity constraints arise from relationships in the ER model.
Each arrow represents a referential integrity constraint.
The source of the arrow is called a foreign key.
• DBMS checks ref. integrity when a referencing value is inserted or changed
– when an order is entered, the entered customer
id must exist in Customers
– if the customer associated with an order is changed, the new value must exist in Customers
• user is asked for a valid value
• DBMS checks ref. integrity when a tuple in the referenced relation is deleted or its primary key is modified e.g.
– if you try to delete a customer tuple and there is an order which references that customer
– if you change the item# of an item which appears on some orders
• If deletion or modification would cause a constraint violation, DBMS has three
• cascade the deletion or modification
– e.g. delete any order associated with the deleted customer
– e.g. change that item# in all orders to be the new item#
• nullify the referencing value
– i.e. set the order customer number to NULL
• reject the deletion or modification
• express general restrictions on the data and changes to it.
– e.g. salary should not exceed 200,000
– e.g. salary cannot decrease
– e.g. an employee’s salary should not be greater than that of the manager of the department.
• Limited support for semantic constraints in some RDBMS.