Integrity Constraints in DBMS

These are basic in the Database Management Systems (DBMS) which are aimed at guaranteeing the quality and consistency of data stored in a database. They also work as rules or conditions under which the data remains valid and by which the relationships between the components of the database are exercised. As integrity constraints are the basic pillar of structure and error-free database, designers of a database and its administrators need to have good knowledge of integrity constraints.

These types of integrity constraints are discussed in this blog together with their importance and how they work in DBMS. We will also offer answers to some of the common questions to help eliminate confusion.

What Are Integrity Constraints?

In the context of a DBMS, integrity constraints refer to rules that have been set at a particular instance so that they limit the kind of data that can be entered into the database to ensure that the latter has high quality. They make sure that the data respects the constraints inherent in a database application.

Key Features of Integrity Constraints

1. Data Accuracy: Make sure that all information which is contained in the database is clean data.
2. Consistency: Reduce chances of having conflicting or two different records in the data entries.
3. Reliability: Make sure that the database in the model is as close as possible to the modeled environment.
4. Automatic Validation: Constraints are bound by the DBMS, thus avoiding the addition of human errors into the process.

Types of Integrity Constraints

1. Domain Constraints

Domain constraints refer to the type of values that can be taken on by a specific attribute in a given relation. These constraints help to define and limit the datatype, the acceptable range as well as format of a value.

Example:

• Any table that has a column comprising the name 'age' can impose the constraint that it only contains positive integers.

CREATE TABLE Person (
   ID INT PRIMARY KEY,
   Name VARCHAR(50),
   Age INT CHECK (Age > 0)
);

Here, we have used the CHECK constraint to prevent the Age value from being negative.

2. Entity Integrity Constraints

Candidate key’s integrity, also known as entity integrity, guarantees that each entity (row) within a table of a database is distinguishable. This is done using a primary key that must ensure that all rows in the table have different key values and none of those values can be NULL.

Example:

• A table used for storing records of students in school needs to have one field that would be unique for each student.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

Here the ‘StudentID’ field is the key field which means that in this table no two records of the students are similar.

3. Referential Integrity Constraints

It gives consistent relationships between the related tables so it is called referential integrity. It provides the ownership or definition of values in the foreign key to German counterparts to the primary key. This eliminates improper referencing and keeps up essential information connections.

Example:

• An example of a table known as a Course table is a foreign key pointing to a Department table.

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Here, the DeptID is a column in the Course table linked to the DeptID column in the Department table. This ensures that every course falls under a proper department hence increasing its validity.

4. Key Constraints

Key constraints define that one or more attributes or some sets of attributes must be unique within a table to identify a record. The two main types of keys used are:

• Primary Key: A name that is used to identify a table.
• Unique Key: This guarantees the uniqueness of a column but permits the attribute to contain a null value.

Example:

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Name VARCHAR(100)
);

Here:

• EmpID is the first candidate key and it refers to a unique identification number of an employee.
• Employees’ emails are another specific key; no two employees have the same email address.

5. NOT NULL Constraints

The NOT NULL constraint guarantees that a column is impossible to contain the NULL value. By its name, it is most often applied to attributes that are required.

Example:

CREATE TABLE Order (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    OrderDate DATE NOT NULL
);

In this case, CustomerName and OrderDate must have values at all times.

6. Default Constraints

A default constraint determines the form of data that will be input in a particular column if the columnist fails to input any value.

Example:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(50),
    Price DECIMAL(10, 2) DEFAULT 0.0
);

Here for example if no value is provided for the Price it is automatically given as 0.0.

7. Check Constraints

In the CHECK constraint, one has the possibility of putting down a condition under which the values in a column must be entered. This is used to confirm that the domain constraints are used frequently and often to enforce the domain constraints.

Example:

CREATE TABLE Vehicle (
    VehicleID INT PRIMARY KEY,
    Model VARCHAR(100),
    Year INT CHECK (Year >= 2000 AND Year <= 2024)
);

This guarantees the validity of the Year column where it can only accept values between 2000 and 2024 only.

8. Assertion Constraints

Assertion constraints are related to the correspondence of elements in the whole database. They are used to enforce elaborate rules and conditions with respect to payment amounts, security, interest, and others.

Example:

CREATE ASSERTION MinimumBalance
    CHECK (
      NOT EXISTS (
        SELECT *
        FROM Account
        WHERE Balance < 100
      )
);

Here, the assertion ensures that no account has a balance of less than 100.

Importance of Integrity Constraints

1. Data Validation: The input validation helps to keep out wrong data from finding its way into the database.
2. Error Prevention: Helps to minimize the probability of having data corruption and types of logical errors.
3. Data Consistency: Ensures consistency mainly across related tables.
4. Efficient Querying: Assists in the process of data retrieval of credible and precise information.
5. Automated Enforcement: Reduces the workload on the XYZ Company by eradicating the need to conduct checks manually.

Conclusion

Constraints in DBMS are further classified into entity integrity constraints and referential integrity constraints these constraints help in maintaining the data integrity. These constraints work at a database level and by so doing, assist in the regulation of data quality and limiting of errors.

Knowledge of the realization of these constraints is mandatory for a developer who deals with databases or for a system administrator and a data analyst. If used appropriately, these constraints will help in developing good and reliable database systems.