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
- 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.
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.