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.

11 Comments

  • Have you ever considered about including a little bit more than just your articles? I mean, what you say is important and everything. Nevertheless think about if you added some great photos or videos to give your posts more, “pop”! Your content is excellent but with images and videos, this website could undeniably be one of the best in its field. Awesome blog!

  • I have been exploring for a little bit for any high quality articles or blog posts on this kind of area . Exploring in Yahoo I at last stumbled upon this web site. Reading this info So i’m happy to convey that I’ve an incredibly good uncanny feeling I discovered just what I needed. I most certainly will make sure to do not forget this website and give it a glance on a constant basis.

  • I believe what you typed made a lot of sense. However, what about this? what if you added a little information? I am not saying your content isn’t solid., however suppose you added something that grabbed people’s attention? I mean BLOG_TITLE is kinda vanilla. You might peek at Yahoo’s home page and note how they create article headlines to get people to click. You might add a video or a pic or two to get readers interested about everything’ve got to say. In my opinion, it could make your website a little bit more interesting.|

  • I’ve been browsing on-line greater than 3 hours as of late, yet I by no means discovered any interesting article like yours. It’s beautiful value sufficient for me. In my view, if all webmasters and bloggers made just right content material as you did, the net will be much more helpful than ever before.|