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.