SQL Server Constraints: Types, Definition and Syntax 


Definition:

Constraints are nothing, they are some Set of rules that are enforced in a single column or multiple columns on tables that dictates/limits/restricts what data can be inserted, 

updated or deleted in columns of a table.

Constraint                              Description

  • NOT NULL                    values cannot be null
  • UNIQUE                        values cannot match any older value
  • PRIMARY KEY             used to uniquely identify a row
  • FOREIGN KEY             References a row in another table
  • CHECK                          Validates condition for new value
  • DEFAULT                      Set default value if not passed
  • INDEX                           Used to speedup the read process

Syntax:

CREATE TABLE Table_Name

(

Column1 Data_type Constraint,

Column2 Data_type(size) Constraint,

….

);

Types of SQL constraints:-

  • NOT NULL constraint :  NOT NULL constraint is used to prevent inserting NO NULL value into the specified column.We can use this constraint while creating or modifying the table.

Advantage:

Missing data can affect the summary of our data & the insights we get from them.

Example:

CREATE TABLE Employees (

Empid INT ,

Name VARCHAR2(25) NOT NULL,

Age INT

);

(In the above example “EmpName” will not accept NULL values because NOT NULL constraint is used with these columns)

  • DEFAULT constraint: It is used to set the default value when the value is not specified.

Example:

CREATE TABLE Employees(

Empid INT ,

Name VARCHAR2(25),

Age INT ,

Salary DEFAULT 20000

);

  • CHECK Constraint : It makes sure that the value that can be inserted into a column satisfies a specific condition.We can use this on create and alter table command.

Advantages : This minimises the amount of code that must be written by the programming staff and also increases the accuracy of data stored.

Example :

CREATE TABLE Employees(

Empid INT,

Name VARCHAR2(25) ,

Age INT CHECK(age>15);

(In the above example the value for age must be greater than 15)

CREATE TABLE Employees(

Empid INT,

Name VARCHAR2(25) CHECK(Name != ‘Sarita’),

Age int);

  • UNIQUE Constraint :The UNIQUE constraint only allows the values in a column that are different.

Example:

CREATE TABLE Person (

    personid INT NOT NULL UNIQUE,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age INT,

    PRIMARY KEY (PersonId)

);

  • PRIMARY KEY constraint :  Primary key is a combination of NOT NULL and UNIQUE constraints. A table can have only one field as primary key.

Example:

CREATE TABLE Person (

    personid INT NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age INT,

    PRIMARY KEY (PersonId)

);

  • FOREIGN KEY Constraint : This is also known as reference key.

Example:

CREATE TABLE Orders (

    Orderid INT NOT NULL,

    OrderNumber INT NOT NULL,

    Personid INT,

    PRIMARY KEY (OrderId),

    FOREIGN KEY (PersonID) REFERENCES Person(PersonId)

);

(Here Personid is foreign key, referencing from table person)

How to Remove the Constraint 

ALTER TABLE command helps to remove the constraint 

Example:

ALTER TABLE Table_Name

ALTER COLUMN Column_Name Data Type Constraint;

  • INDEX : This constraint creates an index on one or more columns to improve query performance.

Example:

CREATE TABLE Employees (

Empid INT ,

Name VARCHAR2(25) NOT NULL,

Age INT

);

CREATE INDEX Employee_Index

ON Employees(Age);