Integrity Constraints in DBMS - Types and Examples

What is Integrity Constraints in DBMS? - Integrity constraints in DBMS play a crucial role in maintaining the quality and integrity of information in a database management system (DBMS). These constraints act as a set of rules that ensure data insertion, updating, and other processes are performed in a way that does not compromise the integrity of the data. In this article, we will explain integrity constraints in DBMS and explore the different types of integrity constraints and their significance in safeguarding the accuracy and reliability of a database.

Types of Integrity Constraints in DBMS

1. Domain Integrity Constraints in DBMS

Domain constraints define integrity constraints in DBMS as the valid set of values for an attribute in a database. Each attribute has a specific data type: string, character, integer, time, date, currency, etc. The domain constraint ensures that the value of an attribute falls within the defined data type. For example, if the domain constraint for a "Name" attribute is set to a string of a maximum of 50 characters, any data inserted into that attribute must comply with this constraint.

Domain constraints are defined for each attribute in a database to ensure that the data entered into the attribute adheres to the specified data type. For example, if the "Age" attribute is defined as an integer, any value entered into it must be a whole number. This constraint helps maintain data integrity and prevents incorrect or irrelevant data insertion. By defining domain constraints, the DBMS can validate the input and reject any data that does not meet the specified criteria.

Examples:

Data Type Constraints: These constraints specify the type of data that can be stored in a column. For example:

• In a "DateOfBirth" column, you can enforce a date data type constraint to ensure that only valid dates are stored.
• In a "PhoneNumber" column, you can enforce a string data type constraint to store phone numbers as text.

Range Constraints: Range constraints restrict the allowable values for a column within a specified range. For example:

• In an "Age" column, you can enforce a constraint that allows values between 0 and 120 to ensure that ages are within a reasonable range.
• In a "Price" column, you can specify that prices must be greater than or equal to zero to prevent negative values.

Enum Constraints: Enumerated (enum) constraints define a list of allowable values for a column. For example:

• In a "Gender" column, you can enforce an enum constraint to allow only values like "Male," "Female," or "Non-Binary."
• In a "Status" column, you can specify that it should only contain values like "Active," "Inactive," or "Pending."

2. Entity Integrity Constraints in DBMS

Entity integrity constraints over relations in DBMS focus on maintaining the uniqueness and validity of primary key values within a table. The primary key serves as a unique identifier for individual rows in a relation. An entity integrity constraint states that the primary key value cannot be null. This is because it becomes impossible to identify and distinguish rows within a table without a valid primary key value. However, other fields in a table can contain null values.

To illustrate this, consider a table called "Customers" with attributes such as "Customer_ID," "Name," and "Address." The "Customer_ID" attribute is designated as the primary key. This constraint ensures that every row in the "Customers" table has a unique customer ID, allowing for easy identification and retrieval of specific customer records. It also prevents the insertion of null values in the primary key field, ensuring the integrity of the data.

Examples:

Consider a database for a library system with a "Books" table. In this table, the "book_id" column serves as the primary key, uniquely identifying each book in the library. An entity integrity constraint for this table would be:

Entity Integrity Constraint Example:

In the "Books" table:

• The "book_id" column is defined as the primary key.
• This constraint ensures that each value in the "book_id" column is unique.
• It also ensures that the "book_id" column cannot contain NULL values.

3. Referential Integrity Constraints in DBMS

What are referential integrity constraints in DBMS? - Referential integrity constraints in DBMS establish a relationship between two tables in a DBMS. These constraints ensure that if a foreign key in Table 1 references the primary key of Table 2, every value of the foreign key in Table 1 must either be null or exist in Table 2. This constraint prevents inconsistencies and data integrity issues when referencing data across different tables. By enforcing referential integrity, the DBMS guarantees that the relationships between tables are maintained accurately.

For example, consider two tables: "Customers" and "Orders." The "Customers" table has a primary key called "Customer_ID," while the "Orders" table has a foreign key called "Customer_ID" that references the primary key in the "Customers" table. The referential integrity constraint ensures that any value entered into the "Customer_ID" field in the "Orders" table must exist in the "Customers" table. This constraint prevents the creation of orphaned rows and maintains the integrity of the data across tables.

Examples:

Imagine a scenario involving two lists: a list of "Tasks" and a list of "Categories" for organizing those tasks.

• Tasks List: This list contains various tasks that need to be completed.
• Categories List: This list contains categories or labels that can be associated with tasks to organize them.

Now, we want to apply referential integrity constraints to ensure that tasks can only be assigned to categories that actually exist in the "Categories" list.

Here's how this would work:

You have a task called "Finish Report" that you want to categorize as "Work."

To enforce referential integrity, you check whether the "Work" category exists in the "Categories" list.

If "Work" exists in the "Categories" list, you can successfully assign the "Finish Report" task to the "Work" category.

However, if you try to assign the "Finish Report" task to a non-existent category like "Vacation," the referential integrity constraint would prevent it, ensuring that tasks are only associated with valid categories.

4. Key Constraints

Keys are entity sets used to identify entities within their respective sets uniquely. While an entity set can have multiple keys, there is typically one designated primary key. The primary key must contain a unique value for each row in the relational table. However, a primary key can also allow null values as long as they remain unique within the table.

Key constraints play a vital role in ensuring the uniqueness of data within a table. While an entity set can have multiple keys, there is typically one designated primary key. The primary key must contain a unique value for each row in the relational integrity constraints in DBMS. However, a primary key can also allow null values as long as they remain unique within the table.

In addition to the primary key, there can be other key constraints, such as unique keys and candidate keys. A unique key constraint ensures that the values in a specific attribute or combination of attributes are unique across the table. This constraint helps maintain data integrity by preventing duplicate values from being inserted. On the other hand, candidate keys are attributes or combinations of attributes that could serve as the primary key. They must also guarantee uniqueness within the table.

Examples:

• Primary Key Constraint: The primary key constraint is used to uniquely identify each record (row) in a database table. It enforces the uniqueness of values in the specified column or set of columns and ensures that no two records have the same primary key value. For example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

In this example, the StudentID column is defined as the primary key, ensuring that each student has a unique identifier.

• Unique Constraint: The unique constraint ensures that values in a specified column or columns are unique across all rows in a table, but it doesn't require them to be the primary key. For example:

CREATE TABLE Employees (
    EmployeeID INT UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Here, the EmployeeID column must contain unique values, but it's not necessarily the primary key.

• Foreign Key Constraint: The foreign key constraint establishes a relationship between two tables by ensuring that the values in one table's column match values in another table's primary key column. It helps maintain referential integrity. For example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

In this case, the CustomerID column in the Orders table references the CustomerID column in the Customers table, ensuring that orders are associated with valid customers.

• Check Constraint: A check constraint specifies a condition that must be satisfied for data to be entered into a column. It allows you to enforce rules or conditions on the data. For example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2) CHECK (Price > 0)
);

This check constraint ensures that the Price column always contains a positive value.

• Not Null Constraint: The not null constraint ensures that a column cannot contain null values, meaning it must have a value in every row. For example:

CREATE TABLE Addresses (
    AddressID INT PRIMARY KEY,
    Street VARCHAR(100) NOT NULL,
    City VARCHAR(50),
    State VARCHAR(50)
);

In this case, the Street column must always have a value.

Benefits of Implementing Integrity Constraints

Implementing integrity constraints in DBMS offers several benefits, including:

• Data Accuracy: The DBMS ensures that only valid and relevant data is stored in the database by enforcing domain constraints. This improves the accuracy and reliability of the information.
• Data Consistency: Integrity constraints in DBMS, such as referential integrity, prevent inconsistencies and anomalies that may arise when referencing data across tables. This ensures that data remains consistent and coherent throughout the database.
• Data Security: Integrity constraints safeguard against accidental damage to the database. By enforcing constraints on data insertion, updating, and other processes, the DBMS protects the integrity and security of the data.
• Enhanced Performance: With the help of integrity constraints in DBMS, the DBMS can optimize query execution and data retrieval. The system can efficiently process and retrieve information by ensuring data integrity, leading to improved performance.

Conclusion

Integrity constraints are a fundamental aspect of database management systems. They ensure the quality and integrity of data by enforcing rules and constraints on data insertion, updating, and referencing processes. By implementing domain constraints, entity integrity constraints, referential integrity constraints, and key constraints, a DBMS can maintain the data's accuracy, consistency, and security. Understanding and implementing these integrity constraints is crucial for organizations to ensure the reliability and effectiveness of their databases.

In conclusion, integrity constraints in DBMS are essential for maintaining a high-quality database that can be relied upon for accurate and consistent information. To gain profound expertise in integrity constraints, enrolling in a web development course is crucial. Explore our "pay after placement" courses for comprehensive learning opportunities. By adhering to these constraints, organizations can enhance data integrity, security, and performance. Implementing and enforcing integrity constraints in DBMS should be a standard practice in any DBMS to ensure the reliability and effectiveness of the database.