What is SQL constraints

Hello everyone, I am going to share the brief about SQL constraints.

SQL constraints

To specify set of rules to limit the type of data in a table we used SQL constraints.

Following are the most used constraints in SQL

1. NOTNULL
2. UNIQUE
3. PRIMARYKEY
4. FOREIGNKEY
5. CHECK
6. DEFAULT
7. INDEX

  

1:- NOT NULL

This  constraint is used to void the acceptance of null value in the column of the table. by default a column has NULL constraints means that columns can hold the null value.

Example

Following is the example to apply NOT NULL Constraint on CREATE TABLE

CREATE TABLE Employee

(

Id int NOT NULL CHECK (Id>0),

Name varchar(255) NOT NULL,

Address varchar(255),

Age int,
)


you can also apply NOT NULL constraint on existing table as below query

ALTER TABLE Employee

MODIFY age  INT NOT NULL;

2:- UNIQUE

UNIQUE constraint is used for the uniqueness of the value for the column in the table. UNIQUE constraint can have multiple in a table.

Example

Following is the example to apply UNIQUE Constraint on single column in CREATE TABLE

CREATE TABLE Employee

(

Id int NOT NULL UNIQUE,

Name varchar(255) NOT NULL,

Address varchar(255),

Age int,
)

Following is the example to apply UNIQUE Constraint on multiple column in CREATE TABLE

CREATE TABLE Employee

(

Id int NOT NULL UNIQUE,

MobileNO int NULL UNIQUE,

Name varchar(255) NOT NULL,

Address varchar(255),

Age int,
)

Following is the example to apply UNIQUE Constraint on ALTER TABLE

ALTER TABLE Employee

ADD CONSTRAINT chk_ Id UNIQUE

Following is the example to DROP a UNIQUE Constraint

ALTER TABLE Employee

DROP CONSTRAINT chk_ Id

3:- Check

CHECK constraints CHECK constraint is used to limiting the values that are accepted by one or more columns.

Following is the example to apply SQL CHECK Constraint on CREATE TABLE

CREATE TABLE Employee

(

Id int NOT NULL CHECK (Id>0),

Name varchar(255) NOT NULL,

Address varchar(255),

Age int,
)

Following is the example to apply SQL CHECK Constraint on ALTER TABLE

ALTER TABLE Employee

ADD CONSTRAINT chk_Employee CHECK (Id>0 AND age> 20)

Following is the example to DROP a CHECK Constraint

ALTER TABLE Employee

DROP CONSTRAINT chk_Employee

4:- PRIMARY KEY

Primary key is cloumn in a table that uniquely identify the earch rows.

A table have can only one primary key and primary can not be hold the null value.

Following is the example to apply PRIMARY KEY Constraint on CREATE TABLE

CREATE TABLE Employee

(

Id INT NOT NULL,

Name varchar(255) NOT NULL,

Address varchar(255),

Age INT,

CONSTRAINT pk_EmployeeID PRIMARY KEY (Id)

)

Following is the example to apply PRIMARY KEY Constraint on ALTER TABLE

ALTER TABLE Employee

ADD PRIMARY KEY (Id)

Following is the example to DROP a PRIMARY KEY Constraint

ALTER TABLE Employee

DROP CONSTRAINT pk_EmployeeID

5:- FOREIGN KEY

FOREIGN  is column in a table that is the primary key column in another table. a table  can have  multiple foreign column in a table . It can hold multiple null values.

Example: Below are the EMPLOYEE and EMPSALARY table.

EMPLOYEE table:

CREATE TABLE EMPLOYEE(

       ID   INT              NOT NULL,

       NAME VARCHAR (20)     NOT NULL,

       AGE  INT              NOT NULL,

       ADDRESS  CHAR (25) ,

       SALARY   DECIMAL (18, 2),      

      CONSTRAINT pk_EmployeeID PRIMARY KEY (ID)

);

EMPSALARY table:

CREATE TABLE EMPSALARY (

       ID          INT       NOT NULL,

       DATE        DATETIME,

       EMPLOYEE_ID INT references EMPLOYEE(ID),

       AMOUNT     double,

       CONSTRAINT pk_ EMPSALARYID PRIMARY KEY (ID)

);

in above  EMPSALARY table EMPLOYEE_ID is the foreign key that refrenece to the primary key in the EMPLOYEE table.