Hello Friends, In this tutorial, I am going to explain you about Constraints in SQL Server.Maintaining Data Integrity and accuracy is a big challenge in SQL Server. Here I am going to cover different types of Constraints in SQL Server.

After completing this tutorial you will be able to understand following points:

  • Constraints in SQL Server.
  • Different Types of Constraints in SQL Server.
  • How to apply Constraints on a column in SQL Server.

Introduction:

Constraints are used to prevent unwanted data in any columns. Constraints are a simple set of rules and restrictions that applied on columns to maintain accuracy and reliability of the data. We can create one or more than one Constraints on a table.

Constraints

We can create Constraints either on table level or on column level.

Column Type Constraints:

These types of constraints are created when the table is created. Here is the syntax for defining column type Constraints:

CREATE TABLE Employee

(

EmployeeID INT PRIMARY KEY,

Name VARCHAR(100),

Age INT,

)

You can see we have defined a Primary Key Constraint while creating Employee table.

Table Type Constraints:

As we have defined a Primary Key Constraints while table creation. Now suppose I want to add some more constraints on the table like I want to add a Check Constraints for on column Age that the age should be Between 18 to 30. Here we can achieve this scenario.

ALTER TABLE Employee


ADD CONSTRAINT chkAge CHECK(Age BETWEEN 18 AND 30)

ADD CONSTRAINT is used to define a TABLE Type Constraints.

SQL Server provides six types of Constraints.

  • Not Null Constraint
  • Check Constraint
  • Default Constraint
  • Unique Constraint
  • Primary Key Constraint
  • Foreign Key Constraint

1-Not Null Constraint:

Not Null Constraint is used to avoid Null values into a column. You cannot ignore a Column value on that column that has Not NULL Constraint defined on it.

Defining Not Null on Column Level:

Syntax:

CREATE TABLE TableName


(

ColumnName DataType CONSTRAIN ConstraintName NOT NULL

)

WHERE ConstraintName is the name of NOT NULL Constraint.

Example:

CREATE TABLE EmployeeDetails

(

ID INT,

Name VARCHAR(100) CONSTRAINT conNotNull NOT NULL,

Age INT

)


Defining Not Null on table level:

Syntax:

ALTER TABLE TableName

ALTER COLUMN CoulmnName DataType NOT NULL

Example:

ALTER TABLE EmployeeDetails

ALTER COLUMN Name VARCHAR(100) NOT NULL

2- Check Constraint:

Check Constraint is used to check a specific condition before inserting data into the table. This type of Constraint is useful for you in case If you have criteria’s on data such as you want to insert data for those Employees that have age between 18 to 30 or Salary should be greater than 10000.

Defining Check Constraint on Column Level:

Syntax:

CREATE TABLE TableName

(

ColumnName DataType CONSTRAINT ConstraintName  (Condition)

)

Example:

CREATE TABLE EmployeeDetails

(

ID INT,

Name VARCHAR(100) CONSTRAINT conNOTNULL NOT NULL,

AGE INT CONSTRAINT conCheckAge CHECK(Age BETWEEN 18 AND 30)

)

Defining Check Constraint on Table Level:

Syntax:

ALTER TABLE TableName

ADD CONSTRAINT ConstraintName CHECK(Condition)

Example:

ALTER TABLE EmployeeDetails

ADD CONSTRAINT conAge CHECK(Age BETWEEN 18 AND 30)

3- Default Constraint:

This Constraint is used to specify default values for the column when no values are provided for that column.

Defining Default Constraint on Column Level:

Syntax:

CREATE TABLE TableName


(

ColumnName DataType CONSTRAINT ConstraintName DEFAULT(Value)

)

Example:

CREATE TABLE EmployeeDetails

(

ID INT,

Name VARCHAR(100) CONSTRAINT conNOTNULL NOT NULL,

AGE INT CONSTRAINT conCheckAge CHECK(Age BETWEEN 18 AND 30),

EmaiID VARCHAR(150) CONSTRAINT conEmailDefault DEFAULT(‘None’)

)

Defining Default Constraint on Table Level:

Syntax:

ALTER TABLE TableName

ADD CONSTRAINT ConstraintName DEFAULT(Value)

Example:

ALTER TABLE EmployeeDetails

ADD CONSTRAINT conEmailDefault DEFAULT(‘None’)

4- Unique Key Constraint:

Unique Key Constraint is used to ensure that the column must have unique values into it. Unique Key is similar to Primary expect the fact that Unique key allows only one Null Value. You can define one or more Unique Key Constraint on a table.

Defining Unique Key Constraint on Column Level:

Syntax:

CREATE TABLE TableName


(

ColumnName DataType CONSTRAINT ConstraintName Unique

)

Example:

CREATE TABLE EmployeeDetails

(

ID INT,

Name VARCHAR(100) CONSTRAINT conNOTNULL NOT NULL,

AGE INT CONSTRAINT conCheckAge CHECK(Age BETWEEN 18 AND 30),

EmaiID VARCHAR(150) CONSTRAINT conEmailDefault DEFAULT(‘None’),

ManagerID INT CONSTRAINT conUnique UNIQUE

)

Defining Unique Key Constraint on Table Level:

Syntax:

ALTER TABLE TableName

ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)

Example:

ALTER TABLE EmployeeDetails

ADD CONSTRAINT conUnique UNIQUE(ManagerID)

5- Primary Key Constraint:

Primary Key Constraint is used to identify uniquely each row in a table. Primary Key does not allow Null value similar to Unique Key. Also Primary Key does not allow duplicate record.

Defining Primary Key Constraint on Column Level:

Syntax:

CREATE TABLE TableName


(

ColumnName DataType CONSTRAINT ConstraintName Primary Key

)

Example:

CREATE TABLE EmployeeDetails

(

ID INT CONSTRAINT conPrimaryKey PRIMARY KEY,

Name VARCHAR(100) CONSTRAINT conNOTNULL NOT NULL,

AGE INT CONSTRAINT conCheckAge CHECK(Age BETWEEN 18 AND 30),

EmaiID VARCHAR(150) CONSTRAINT conEmailDefault DEFAULT(‘None’),

ManagerID INT CONSTRAINT conUnique UNIQUE

)

Defining Primary Key Constraint on Table Level:

Syntax:

ALTER TABLE TableName

ADD CONSTRAINT ConstraintName PRIMARY KEY(ColumnName)

Example:                       

ALTER TABLE EmployeeDetails

ADD CONSTRAINT conPrimaryKey PRIMARY KEY(ManagerID)

6- Foreign Key Constraint:

Foreign Key Constraint is used to build a relation between two tables. The Foreign Key is a column in a database table that is a Primary Key in another table.

Defining Primary Key Constraint on Column Level:

Syntax:

CREATE TABLE TableName


(

ColumnName DataType CONSTRAINT ConstraintName REFERENCES ReferenceTableName(ReferenceColumnName)

}

Example:

CREATE TABLE EmployeeAddressDetails

(

ID INT CONSTRAINT conPrimaryKey,

EmployeeID INT CONSTRAINT conForeignKey REFERENCES Employee(ID),

Street VARCHAR(100),

State VARCHAR(100),

City VARCHAR(100)

}

Conclusion:

I hope this is a useful tutorial is help for you.

Thank You.

Leave a Comment