Constraints in SQL Server - A Beginner's Guide

Constraints in SQL Server

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT
• AUTO_INCREMENT

Wish to crack SQL interviews? Intellipaat’s Top SQL Interview Questions are meant only for you!

• NOT NULL

  • NOT NULL Constraints on CREATE TABLE

Below is an example of NOT NULL SQL Server constraints “UserID”, “FirstName”, and “LastName” columns will NOT accept NULL values when the “Mytable” table is created.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
JobPosition varchar(255),
);

• • NOT NULL Constraints on ALTER TABLE

Below is an example to create a NOT NULL ALTER TABLE SQL Server constraints on the “JobPosition” column when the “Mytable” table is created.

Example

ALTER TABLE Mytable
MODIFY JobPosition varchar(255) NOT NULL;

UNIQUE

Unique constraints are used to ensure that all the values which are in a column must be different.

• • UNIQUE Constraints on CREATE TABLE

Below is an example to create a UNIQUE constraint on the “UserID” column in the table “Mytable”.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
UNIQUE (UserID)
);

Use the following SQL syntax to name a UNIQUE constraint and define a UNIQUE constraint on several columns.

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
CONSTRAINT UC_Mytab UNIQUE (UserID, JobPosition)
);

• • UNIQUE Constraints on ALTER TABLE

Below is an example to create a UNIQUE constraint on the “UserID” column in the existing table.

Example

ALTER TABLE Mytable
ADD UNIQUE (UserID);

Use the following SQL syntax to name a UNIQUE constraint and define a UNIQUE constraint on several columns.

ALTER TABLE Mytable
ADD CONSTRAINT UC_Mytab UNIQUE (UserID, JobPosition);

Go through SQL Server Tutorial for a deeper understanding.

• • UNIQUE Constraints on DROP TABLE

Below is an example to DROP a UNIQUE Constraint.

ALTER TABLE Mytable
DROP INDEX UC_Mytab;

PRIMARY KEY

In a table, the PRIMARY KEY constraint identifies each record in a unique manner.

• • PRIMARY KEY ON CREATE TABLE

Below is an example to create a PRIMARY KEY on the “UserID” column in a table called “Mytable”.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
PRIMARY KEY (ID)
);

Use the following SQL syntax to enable the naming of a PRIMARY KEY constraint and to define a PRIMARY KEY constraint on multiple columns.

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition)
);

• • PRIMARY KEY ON ALTER TABLE

Below is an example to create a PRIMARY KEY on the “UserID” column in the existing table.

Example

ALTER TABLE Mytable
ADD PRIMARY KEY (ID);

Use the following SQL syntax to name a PRIMARY KEY constraint and define a PRIMARY KEY constraint on multiple columns.

ALTER TABLE Mytable
ADD CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition);

• • PRIMARY KEY ON DROP TABLE

Below is an example to DROP a PRIMARY KEY constraint.

ALTER TABLE Mytable
DROP PRIMARY KEY;

Enroll yourself for SQL Server certification and give a head-start to your career in SQL!

FOREIGN KEY

• • FOREIGN KEY ON CREATE TABLE

A field or column that joins two tables together is known as a foreign key.
Below is an example of a Foreign Key Constraint.
There are two tables named “Customer” & “Orders”.

Example

Customer Table
CREATE TABLE Customer (
CustomerID int NOT NULL PRIMARY KEY,
Name varchar(45) NOT NULL,
Age int,
City varchar(25)
);

Orders Table

CREATE TABLE Orders (
Order_ID int NOT NULL PRIMARY KEY,
Order_Num int NOT NULL,
CustomerID int,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Here, you can see that the “CustomerID” field in the “Orders” table points to the “CustomerID” field in the “Customer” table. The “CustomerID” is the PRIMARY KEY in the “Customer” table, while the “CustomerID” column of the “Orders” table is a FOREIGN KEY.

CHECK

A check constraint limits the value in a particular column and is used to consider whether the value for that column is valid or not under the provided conditions.

• • CHECK Constraints on CREATE TABLE

Below is an example of CHECK constraints on the “VoterAge” column in a table named “Mytable” which will ensure that a user’s age must be 18 or more.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
VoterAge int,
CHECK (VoterAge>=18)
);

• • CHECK Constraints on ALTER TABLE

Below is an example of creating CHECK constraints on the “VoterAge” column in an existing table.

Example

ALTER TABLE Mytable
ADD CHECK (VoterAge>=18);

• • CHECK Constraints on DROP TABLE

Below is an example to DROP a CHECK constraint from the table.

Example

ALTER TABLE Mytable
DROP CHECK CHK_VoterAge;

Preparing for the SQL interviews? Check out these SQL Server Interview Questions.

DEFAULT

To specify a default value for a column, the DEFAULT Constraint is used.

• • DEFAULT Constraints on CREATE TABLE

Below is an example that sets a DEFAULT value in the column “JobPosition” in a table named “Mytable”.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255) DEFAULT 'Technical'
);

• • DEFAULT Constraints on ALTER TABLE

Below is an example to set a DEFAULT value in the column “JobPosition” in an existing table.

Example

ALTER TABLE Mytable
ALTER JobPosition SET DEFAULT 'Technical’;

• • DEFAULT Constraints on DROP TABLE

Below is an example to DROP a DEFAULT Constraint from the table.

ALTER TABLE Mytable
ALTER JobPosition DROP DEFAULT;

AUTO_INCREMENT

A function called Auto Increment works with numeric Data Types. Each time a record is added to a table, it instantly generates a series of numerical numbers.

Below is an example where the UserID column will be auto-incremented in the table named “Mytable”.

Example

SQLServer> CREATE TABLE Mytable(
UserID int NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (UserID)
);

Next, we need to insert the values into the table “Mytable”.

SQLServer> INSERT INTO Mytable (name) VALUES
('Himanshu'),('Yash'),('Ashish'),
('Anil'),('Mukul'),('Ravi');

And automatically SQL Server will generate a series of numerical numbers.