SQL Constraints In DBMS

 

Do we need constraints?

SQL Constraints are set of rules and restrictions which are applied to the columns or table of the database. This constraint ensures below points.

• Data consistency
• No accidental data loss by deleting reference data.
• Reduce data redundancy.

Table Level Constraints

• PRIMARY KEY
• FORIEGN KEY

PRIMARY KEY

Primary Key constraint ensure that column doesn't contain NOT NULL value and unique. It will be used to identify particular row from the table. Table is by default index on the primary key column.

Below sample code creates EMP_ID as primary key for EMPLOYEE table which is auto increment by one every time a new record inserted in the table.

CREATE TABLE EMPLOYEE (

    EMP_ID INT PRIMARY KEY AUTO INCREAMENT NOT NULL,

    NAME VARCHAR (200),

    EMAIL VARCHAR (200),

AGE INT,

    PHONE_NUMBER VARCHAR (15)

    ISACTIVE CHAR (1)

)

FOREIGN KEY

Foreign Key is used to relate two tables. The relationship between the two tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

• This is also called a referencing key.
• We use ALTER statement and ADD statement to specify this constraint.

In the below sample code, department id is used as foreign key in the employee table.

CREATE TABLE DEPARTMENT (

    ID INT PRIMARY KEY NOT NULL,

    NAME VARCHAR(200) NOT NULL UNIQUE

);

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY NOT NULL,

    NAME VARCHAR(200) NOT NULL,

    EMAIL VARCHAR(200) UNIQUE,

    AGE INT NOT NULL CHECK (AGE < 150),

    PHONE_NUMBER VARCHAR(15)

    ISACTIVE CHAR(1)

    CREATED_DATE DATE DEFAULT CURRENT_TIMESTAMP,

    DEPT_ID INT FOREIGN KEY REFERENCES DEPARTMENT(ID)

);

Foreign key has below behavior on updating or deleting records from the table.

• RESTRICT - This is default behavior of the foreign key. It will not allow to update or delete records if it has reference rows in another tables.
• SET NULL - This action set reference column value as NULL on UPDATE/DELETE.
• NO ACTION
• CASCADE - This action set reference column value with new value on updating column value. In case of deleting row, it deletes reference rows from other referencing table.

Column Level Constraints

• NOT NULL
• DEFAULT
• CHECK
• UNIQUE

This constraint applies restriction or set of rules to single column data. It will not impact entire table data. Below are common constraints which are applied on the column level.

NOT NULL

Default any column in the table can contains the NULL value. In some scenario we want to have data in the column i.e., User table must have valid username and age information in the row. By applying NOT NULL constraint on each column we can ensure that it will not allow to insert record with NULL value in the username and age column.

Sample code to apply NOT NULL constraint:

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY NOT NULL,

    NAME VARCHAR (200) NOT NULL,

EMAIL VARCHAR (200),

    AGE INT NOT NULL,

    PHONE_NUMBER VARCHAR (15)

    ISACTIVE CHAR (1)

)

UNIQUE

It ensures that column has unique value in the table. But it also allows NULL value in the column.

Below sample code apply UNIQUE constraint on the email column of employee table to ensure that table doesn't have two records with same email address.

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY NOT NULL,

    NAME VARCHAR (200) NOT NULL,

    EMAIL VARCHAR (200) UNIQUE,

    AGE INT NOT NULL CHECK (AGE < 150),

    PHONE_NUMBER VARCHAR (15)

    ISACTIVE CHAR (1)

)

DEFAULT

When record inserted into the table without specifying value for a particular column default it will store NULL value. DEFAULT constraint ensures that the column has some given default value when user try to insert record without specifying any value for the column.

For example, Employee table has column called "CREATED_DATE", if we want to have default value as system date and time when record added to table, it can be achieved through below sample code.

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY NOT NULL,

    NAME VARCHAR(200) NOT NULL,

    EMAIL VARCHAR(200) UNIQUE,

    AGE INT NOT NULL CHECK (AGE < 150),

    PHONE_NUMBER VARCHAR(15)

    ISACTIVE CHAR(1)

    CREATED_DATE DATE DEFAULT CURRENT_TIMESTAMP

)

CHECK

This constraint allows to define various rules & restriction which should be validated before inserting records in the table. 

It allows to define rule like column should values within specified range. 

It should check value if it is within predefined list.

In the below sample code, we applied CHECK constraint on the age column to ensure that age value should not be greater than 150.

CREATE TABLE EMPLOYEE (

    ID INT PRIMARY KEY NOT NULL,

    NAME VARCHAR(200) NOT NULL,

    AGE INT NOT NULL CHECK (AGE < 150),

    PHONE_NUMBER VARCHAR(15)

    ISACTIVE CHAR(1)

)

I hope you liked the blog and able to understand the concepts of SQL Constraints in the DBMS.

Thank you. Appreciate your feedback and comments.