Constraints in Database Management System

In a database management system, certain constraints are applied while performing any operation on the database to ensure that the integrity and quality of the data are maintained throughout the process. There are several types of constraints that can be implemented on the database during the CRUD operations and other processes. They limit the entry of data or the data type of the data or both. These Constraints are also known as Integrity Constraints.

Types of Constraints

• Domain Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Key Constraints
• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• Mapping Constraints

Domain constraint: This constraint specifies a set of values that can be entered as the valid input for the attribute. Datatypes of domain attributes include string, integer, date, time etc. A particular column of a table in a database allows only a single type of data. We cannot have any other datatype value in that column.

Domain constraints are also called user-defined data types. Additionally, it enforces some basic rules that are defined by the user on the column.

Domain Constraint is a combination of the datatypes and special constraints and can be expressed as “data type + Constraints (NOT NULL / UNIQUE /                          PRIMARY KEY / FOREIGN KEY / CHECK / DEFAULT)”

Entity Integrity Constraint: This constraint specify that a primary key cannot hold null as a value as it is used to uniquely identify a record but if null, we won’t be able to identify the entity.

Referential Integrity Constraint: This constraint is applied among different tables. According to this constraint if a foreign key of a table refers to the primary key of that table, then all the values of that foreign key either should be a value from the primary key of that other table or should be a null value. 

Key Constraint: They are two types of key constraints. These are primary key constraints and foreign key constraints.

• PRIMARY KEY

As discussed earlier, the Primary key can uniquely identify each record in a table. It must have unique values and cannot contain nulls. In the example below we have declared WORKER_ID as the Primary Key of the table. Now no two values of the WORKER_ID can hold the same value and neither can it hold NULL as a value.

CREATE TABLE WORKER(
WORKER_ID INT NOT NULL,
WORKER_NAME VARCHAR (30) NOT NULL,
WORKER_AGE INT NOT NULL,
WORKER_PHONE_NUMBER INT NOT NULL UNIQUE,
WORKER_ADDRESS VARCHAR (200),
PRIMARY KEY (WORKER_ID)
);

• FOREIGN KEY

Foreign keys are primarily used to refer to the values of another table in the table. It is a key that refers to the primary key of any other table and is allowed to have any value from that primary key column or it should be NULL. They are used to cross-refer the tables.

Not null: This is used to check that no value entered in the particular table should be null. When no value is entered while entering the value of any column a null value is automatically assigned to it. But when we specify this constraint we cannot leave any value empty as the particular column should not have a null value. In such a case, we usually assign a DEFAULT value which is assigned when no value is entered.

Example:

CREATE TABLE WORKER(
WORKER_ID INT,
WORKER_NAME VARCHAR (30) NOT NULL,
WORKER_AGE INT NOT NULL,
WORKER_PHONE_NUMBER INT NOT NULL,
WORKER_ADDRESS VARCHAR (200),
PRIMARY KEY (WORKER_ID)
);

UNIQUE:

UNIQUE as the name suggests this keyword is used to ensure that no two values of the particular column have the same values. In the example below, WORKER_PHONE_NUMBER is limited with this constraint. No, two employees can have the same value in this database.

CREATE TABLE WORKER(
WORKER_ID INT NOT NULL,
WORKER_NAME VARCHAR (30) NOT NULL,
WORKER_AGE INT NOT NULL,
WORKER_PHONE_NUMBER INT NOT NULL UNIQUE,
WORKER_ADDRESS VARCHAR (200),
PRIMARY KEY (WORKER_ID)
);

DEFAULT:

In the DEFAULT constraint, we can assign a specified value to a column, this value is assigned where the value is left by the user empty. If there is no value assigned to WORKER_SALARY, then instead of a NULL value 10000 will be assigned as that employee’s salary.

CREATE TABLE WORKER(
WORKER_ID INT NOT NULL,
WORKER_NAME VARCHAR (30) NOT NULL,
WORKER_SALARY INT DEFAULT 10000,
WORKER_AGE INT NOT NULL,
WORKER_PHONE_NUMBER INT NOT NULL,
WORKER_ADDRESS VARCHAR (200),
PRIMARY KEY (WORKER_ID)
);

CHECK:

This keyword is used to enclose the values of the particular column in a finite set. When we can implement this constraint to determine the extreme values of the column. The value feed to the column should hold to this condition, if not the value won’t be accepted as an entry. Here an error will be raised if the WORKER_SALARY is more than the specified range that is 100000.

CREATE TABLE WORKER(
WORKER_ID INT NOT NULL,
WORKER_NAME VARCHAR (30) NOT NULL,
WORKER_SALARY INT CHECK (WORKER_SALARY <100000),
WORKER_AGE INT NOT NULL,
WORKER_PHONE_NUMBER INT NOT NULL,
WORKER_ADDRESS VARCHAR (200),
PRIMARY KEY (WORKER_ID)
);