Mastering Database Management

0 of 78 lessons complete (0%)

Relational Models

Relational Integrity Constraints

You don’t have access to this lesson

Please register or sign in to access the course content.

Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Integrity Constraints. There are three main integrity constraints −

  • Key constraints
  • Domain constraints
  • Referential integrity constraints

The diagram below shows a conceptual model with two associations: WrittenBy and PublishedBy. The Book entity type has a property, PublisherId, that references the entity key of the Publisher entity type when you define a referential integrity constrainst on the PublishedBy association.

Relational Integrity Constraints

Key Constraints

There must be at least one minimal subset of attributes in the relation, which can identify a tuple uniquely. This minimal subset of attributes is called key for that relation. If there are more than one such minimal subsets, these are called candidate keys.

 Key constraints force that −

  •  In a relation with a key attribute, no two tuples can have identical values for key attributes.
  •  a key attribute can not have NULL values.
  •  Key constraints are also referred to as Entity Constraints.

Domain Constraints

Attributes have specific values in real-world scenario. For example, age can only be a positive integer. The same constraints have been tried to employ on the attributes of a relation. Every attribute is bound to have a specific range of values. For example, age cannot be less than zero and telephone numbers cannot contain a digit outside 0-9.

Referential Integrity Constraints

Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key attribute of a relation that can be referred in other relation.

Referential integrity constraint states that if a relation refers to a key attribute of a different or same relation, then that key element must exist.

 Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table).

foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. 

Integrity

Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data.