Integrity Constraints

Integrity Constraints

• Integrity Constraints are the set of constructs provided by a data model for specifying conditions that must be satisfied by the data.

• An Integrity constraint (IC) is a condition specified on a database schema and restricts the data that can be stored in an instance of the database.

• Integrity constraints can be

ü  Domain Constraints

ü  Key Constraints

ü  Foreign Key Constraints

ü  General Constraints

Domain Constraints

§  Domain refers to a set of possible values for each attribute associated with an entity set.

§  Domain constraints in the schema specify an important condition that the user wants each instance of the relation to satisfy.

 

                Key Constraints

§  A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.

§  Super Key :  It is a set of one or more attributes which put together enable us to identify uniquely an entity in the entity set.

§  Candidate Key : A set of fields that uniquely identifies a tuple according to a key constraint is called a candidate key.  A superkey for which no subset is a superkey is called a candidate key.  ( A superkey that is minimal is a candidate key ) Eg.  In students relation, sid is candidate key.

§  Primary Key : It is a candidate key (there may be more than one) chosen by the database designer to identify entities in an entity set.

                Foreign Key Constraints

§  The most common IC involving two relations is a foreign key constraint.

§  Foreign Key : If the primary key of one table is referenced in the other table, then that key is called as foreign key in the another table.  The foreign key in the referencing relation must match the primary key of the referenced relation.

               General Constraints

§  General constraints : Domain, primary key and foreign key constraints are considered to be a fundamental part of the relational data model.  It is also necessary to specify more general constraints.

§  General constraints are

o   Table Constraints – Table constraints are associated with a single table and checked whenever the table is modified.

o   Assertions – Assertions involve several tables and are checked whenever any of these tables is modified.