Constraints on Relational database model
On modeling the design of the relational database we can put some restrictions like what values are allowed to be inserted in the relation, what kind of modifications and deletions are allowed in the relation. These are the restrictions we impose on the relational database.
In models like ER models, we did not have such features.
Constraints in the databases can be categorized into 3 main categories:
- Constraints that are applied in the data model is called Implicit constraints.
- Constraints that are directly applied in the schemas of the data model, by specifying them in the DDL(Data Definition Language). These are called as schema-based constraints or Explicit constraints.
- Constraints that cannot be directly applied in the schemas of the data model. We call these Application based or semantic constraints.
So here we will deal with Implicit constraints.
Mainly Constraints on the relational database are of 4 types:
- Domain constraints
- Key constraints
- Entity Integrity constraints
- Referential integrity constraints
Let discuss each of the above constraints in detail.
1. Domain constraints :
- Every domain must contain atomic values(smallest indivisible units) it means composite and multi-valued attributes are not allowed.
- We perform datatype check here, which means when we assign a data type to a column we limit the values that it can contain. Eg. If we assign the datatype of attribute age as int, we cant give it values other then int datatype.
In the above relation, Name is a composite attribute and Phone is a multi-values attribute, so it is violating domain constraint.
2. Key Constraints or Uniqueness Constraints :
- These are called uniqueness constraints since it ensures that every tuple in the relation should be unique.
- A relation can have multiple keys or candidate keys(minimal superkey), out of which we choose one of the keys as primary key, we don’t have any restriction on choosing the primary key out of candidate keys, but it is suggested to go with the candidate key with less number of attributes.
- Null values are not allowed in the primary key, hence Not Null constraint is also a part of key constraint.
In the above table, EID is the primary key, and first and the last tuple has the same value in EID ie 01, so it is violating the key constraint.
3. Entity Integrity Constraints :
- Entity Integrity constraints says that no primary key can take NULL value, since using primary key we identify each tuple uniquely in a relation.
In the above relation, EID is made primary key, and the primary key cant take NULL values but in the third tuple, the primary key is null, so it is a violating Entity Integrity constraints.
4. Referential Integrity Constraints :
- The Referential integrity constraints is specified between two relations or tables and used to maintain the consistency among the tuples in two relations.
- This constraint is enforced through foreign key, when an attribute in the foreign key of relation R1 have the same domain(s) as the primary key of relation R2, then the foreign key of R1 is said to reference or refer to the primary key of relation R2.
- The values of the foreign key in a tuple of relation R1 can either take the values of the primary key for some tuple in relation R2, or can take NULL values, but can’t be empty.
In the above, DNO of the first relation is the foreign key, and DNO in the second relation is the primary key. DNO = 22 in the foreign key of the first table is not allowed since DNO = 22
is not defined in the primary key of the second relation. Therefore, Referential integrity constraints is violated here
Attention reader! Don’t stop learning now. Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.
Learn all GATE CS concepts with Free Live Classes on our youtube channel.