Relational databases are widely used; they provide structured and reliable ways to store and access the data. To ensure data integrity and consistency, relational databases mostly rely on certain rules known as constraints. These constraints are used to maintain the quality and reliability of data across different tables and records.
In this chapter, we will take a look at the types of relational model constraints, supplemented with practical examples and explanations for a better understanding.
Relational Model Constraints
Relational model constraints are set of rules applied to the structure and data of a database to maintain logical consistency. They ensure that data is valid according to the rules defined in the database schema. These constraints can be broken down into three main types as given below −
- Inherent model-based constraints − Implied by the nature of the relational model itself.
- Schema-based constraints − Explicitly defined within the database schema and enforced by the database system.
- Application-based constraints − Managed through external application logic as they cannot be directly expressed in the schema.
Types of Relational Model Constraints
Relational model constraints can be of the following types −
- Domain Constraints
- Key Constraints
- Entity Integrity Constraint
- Referential Integrity Constraint
Let’s understand each of these in detail.
Domain Constraints
Domain constraints are used to specify that, every attribute in a database must contain only values from a predefined set known as domain. This ensures that each column in a table holds data of a particular type and format.
For example, consider an EMPLOYEE table with an Age column that only allows integer values between 18 and 65. This domain constraint ensures that inserting a value such as “17” or “70” would violate the rule and be rejected by the system.
If we define Age as INTEGER CHECK (Age BETWEEN 18 AND 65), any attempt to insert Age = 17 will trigger an error. The domain constraints is used to maintain data accuracy by preventing improper data types or out-of-bounds values.
Key Constraints
Key constraints are used for each record within a relation. It can be uniquely identified. This uniqueness is achieved through keys.
Keys can be of the following types −
- Superkey − Any set of attributes that uniquely identifies a tuple in a relation.
- Candidate key − A minimal superkey. Removing any attribute would break the uniqueness property.
- Primary key − A chosen candidate key that uniquely identifies each tuple in the table.
For example, consider a table called STUDENT as given below −
| Name | SSN | Home Phone | Address | Office Phone | Age | GPA |
|---|---|---|---|---|---|---|
| Dick Davidson | 422-11-2320 | NULL | 3452 Elgin Road | (817)749-1253 | 25 | 3.53 |
| Barbara Benson | 533-69-1238 | (817)839-8461 | 7384 Fontana Lane | NULL | 19 | 3.25 |
| Rohan Panchal | 489-22-1100 | (817)376-9821 | 265 Lark Lane | (817)749-6492 | 28 | 3.93 |
| Chung-cha Kim | 381-62-1245 | (817)375-4409 | 125 Kirby Road | NULL | 18 | 2.89 |
| Benjamin Bayer | 305-61-2435 | (817)373-1616 | 2918 Bluebonnet Lane | NULL | 19 | 3.21 |
The SSN (Social Security Number) serves as a primary key because each student must have a unique SSN. The primary key constraint guarantees that no two students can have the same SSN.
This is important because the primary keys help to maintain the identity of each record. If a STUDENT table did not have a primary key, then distinguishing between duplicate records would be difficult, which will lead to a potential data ambiguity.
Entity Integrity Constraint
The entity integrity constraint states that, the primary key of a relation must always have a non-NULL value. It is needed because primary keys are used to identify tuples, and a NULL value would make identification impossible.
Consider a table called CUSTOMER. If Customer_ID is defined as the primary key, then any attempt to insert a record with “Customer_ID = NULL” will violet the entity integrity constraint. It will be rejected by the system.
Practically, we can consider a situation where a banking system has a Customer relation with primary keys representing account numbers. If one record had Account_ID = NULL, then it would be impossible to reference or link this record.
Referential Integrity Constraint
Referential integrity constraint states that, the relationships between tables remain consistent. A foreign key in one table must either match a primary key value in another table or be NULL. This constraint can preserve the logical connections between records in different tables.
Suppose we have an EMPLOYEE table with a Dno column. Here, it references the Dnumber from the DEPARTMENT table. If Dno is set to a value that does not exist in the DEPARTMENT table, then it violates the referential integrity constraint.
Detailed Scenario − See the Employee table −
| Fname | Minit | Lname | SSN | Bdate | Address | Sex | Salary | Super_ssn | Dno |
|---|---|---|---|---|---|---|---|---|---|
| John | B | Smith | 123456789 | 1965-01-09 | 731 Fondren, Houston, TX | M | 30000 | 333445555 | 5 |
| Franklin | T | Wong | 333445555 | 1955-12-08 | 638 Voss, Houston, TX | M | 40000 | 888665555 | 5 |
| Alicia | J | Zelaya | 999887777 | 1968-01-19 | 3321 Castle, Spring, TX | F | 25000 | 987654321 | 4 |
| Jennifer | S | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire, TX | F | 43000 | 888665555 | 4 |
| Ramesh | K | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble, TX | M | 38000 | 333445555 | 5 |
| Joyce | A | English | 453453453 | 1972-07-31 | 5631 Rice, Houston, TX | F | 25000 | 333445555 | 5 |
| Ahmad | V | Jabbar | 987987987 | 1969-03-29 | 980 Dallas, Houston, TX | M | 25000 | 987654321 | 4 |
| James | E | Borg | 888665555 | 1937-11-10 | 450 Stone, Houston, TX | M | 55000 | NULL | 1 |
See the Department table −
| Dname | Dnumber | Mgr_ssn | Mgr_start_date |
|---|---|---|---|
| Research | 5 | 333445555 | 1988-05-22 |
| Administration | 4 | 987654321 | 1995-01-01 |
| Headquarters | 1 | 888665555 | 1981-06-19 |
Valid Insertion − Adding an EMPLOYEE with Dno = 4 is allowed if Dnumber = 4 exists in DEPARTMENT.
Violation Example − Inserting an EMPLOYEE with Dno = 99, where Dnumber = 99 does not exist in DEPARTMENT, would be rejected.
This constraint is particularly useful in maintaining relationships between parent and child tables. So, deleting a department referenced by employees must either be restricted or cascaded to maintain integrity.
Handling Constraint Violations
In this section, let’s understand the kind of operations that can cause constraint violations and what precautionary measures can be taken in order to prevent such operations.
Insertion Violations
An insertion operation can violate various constraints because of −
- Domain Constraint Violation − Inserting a non-integer value in an Age column defined as an integer will be rejected.
- Key Constraint Violation − Adding a tuple with a duplicate Ssn in a STUDENT table where Ssn is the primary key will result in an error.
- Entity Integrity Violation − Attempting to insert a row with NULL as the primary key value violates the entity integrity constraint.
- Referential Integrity Violation − Inserting an EMPLOYEE with a Dno value that does not match any Dnumber in DEPARTMENT.
Deletion and Referential Integrity
Deleting a tuple can trigger constraint violations, particularly for referential integrity. For example, deleting the DEPARTMENT record that is referenced by the Dno of an EMPLOYEE tuple results in referential integrity issues.
We can solve this issue by Restrict operation, like prevent deletion if it causes a violation. Or cascade, that is, automatically delete all dependent records. There is another solution: Set NULL / Default, i.e., modify the foreign key in dependent records to NULL or a default value.
Update Operations
Updating a record can affect primary keys and foreign keys. Modifying a primary key is similar to deleting the original record and inserting a new one, which may violate existing constraints. Updates can be two types:
- Safe Update − Changing an employee’s salary does not affect primary or foreign keys and is permissible.
- Risky Update − Modifying Ssn in EMPLOYEE to an existing Ssn violates the primary key constraint.
Other Constraints and Business Rules
We have studied the rules, but there are some other application-based constraints, or business rules. These rules include those that cannot be easily defined within the schema, such as −
- “An employee’s salary must not exceed that of their supervisor.”
- “Total weekly working hours for an employee should not exceed 56 hours.”
These are typically enforced using application logic or triggers.
Conclusion
In this chapter, we presented an elaborate explanation of how relational model constraints play a crucial role in maintaining data integrity and consistency in a database. We understood the different types of constraints, such as domain constraint, key constraint, entity integrity constraints, and referential integrity constraints. We discussed practical examples for these constraints in action. We also reviewed how constraint violations can be handled and the importance of application-based rules.
Leave a Reply