Constraints in DBMS

  • Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table.
  • The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table. In this tutorial we will learn several types of constraints that can be created in RDBMS.

Types of constraints

  • Integrity Constraints
  • Domain constraints
  • Key Constraints
  • Entity Integrity Constraints
  • Referential Integrity Constraints

Domain constraints

  • Each attribute Ai must be an atomic value from dom( Ai) for that attribute. The attribute, Name in the example is a BAD DESIGN (because sometimes we may want to search a person by only using their last name.

Key Constraints

  • Primary key
  • Foreign keys

Entity Integrity Constraints

  • The primary key attribute, PK, of any relational schema R in a database cannot have null values in any tuple.
  • In other words, for each table in a DB, there must be a key; for each key, every row in the table must have non-null values. This is because PK is used to identify the individual tuples.

Referential Integrity Constraints

  • Referential integrity constraints are used to specify the relationships between two relations in a database.
  • Consider a referencing relation, R1, and a referenced relation, R2. Tuples in the referencing relation, R1, have attributed FK (called foreign key attributes) that reference the primary key attributes of the referenced relation, R2. A tuple, t1, in R1 is said to reference a tuple, t2, in R2 if t1[FK] = t2[PK].
  • A referential integrity constraint can be displayed in a relational database schema as a directed arc from the referencing (foreign) key to the referenced (primary) key.