SQL Constraints

How to create a SQL constraint?

A SQL constraint can be created in a table in two way

• While creating a table – with CREATE TABLE statement
• after a table is created – with ALTER TABLE statement

Commonly used SQL Constraints are described below

NOT NULL Constraint

By default SQL columns accept null values. When we wants to restrict null values from a column then we can apply NOT NULL Constraint. So that it will block null value to that column. in this case an INSERT statement or UPDATE statement cannot be performed without giving a value to particular column.

Example
In the below examples it ensures that Id and Name must not be NULL.
NOT NULL on CREATE table statement

Create table Employee
(
   Id bigint NOT NULL,
   Name varchar(50) NOT NULL,
   Age int
)

NOT NULL on ALTER table statement

alter table Employee alter column Id bigint NOT NULL
alter table Employee alter column Name varchar(50) NOT NULL

UNIQUE Constraint

A UNIQUE Constraint means it ensures all the values in a column is different. Any value that matches the column data type including NULL can be inserted only once in this column. A table can have more than one UNIQUE Constraints.
Example
In the below examples it ensures that Id will not be repeated.
UNIQUE on CREATE table statement

Create table Employee
(
   Id bigint NOT NULL UNIQUE,
   Name varchar(50) NOT NULL,
   Age int
)

UNIQUE on ALTER table statement

alter table users add UNIQUE(Id)

PRIMARY KEY Constraint

PRIMARY KEY contains rules from both NOT NULL and UNIQUE. Which means a PRIMARY KEY column cannot contain NULL value and all values must be different. PRIMARY KEY is used to identify each record with a unique value. A table can have only one PRIMARY KEY. PRIMARY KEY can be applied to a single column or multiple columns together. PRIMARY KEY applied to multiple columns together is called COMPOSITE PRIMARY KEY
Example
Primary key on CREATE TABLE statement

CREATE TABLE users
(
	user_id int PRIMARY KEY,
	username varchar(45) NOT NULL,
	[password] varchar(45) NOT NULL,
	email varchar(45) NOT NULL
)

Primary key on ALTER TABLE statement

ALTER TABLE users ADD PRIMARY KEY(USER_ID)

COMPOSITE Primary key on CREATE TABLE statement

CREATE TABLE users
(
	user_id int NOT NULL,
	username varchar(45) NOT NULL,
	[password] varchar(45) NOT NULL,
	email varchar(45) NOT NULL,
        PRIMARY KEY (user_id ASC, email)
)

COMPOSITE Primary key on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY (user_id ASC, email)

FOREIGN KEY Constraint

FOREIGN KEY Constraint is used to link two tables. A FOREIGN KEY can be only created with the reference of PRIMARY KEY from another table. FOREIGN KEY helps to execute JOIN queries much faster. The FOREIGN KEY containing table is called as Child table and the referenced PRIMARY KEY containing table is called as Parent/referenced table.
Example
Foreign key on CREATE TABLE statement

CREATE TABLE USERADDRESS
(
	Id bigint not null PRIMARY KEY,
	UserId bigint NOT NULL,
	AddresLine1 varchar(200) NOT NULL,
	AddresLine2 varchar(200),
	PIN varchar(12) NOT NULL,
	FOREIGN KEY(UserId) references users(user_id)
)

Foreign key on ALTER TABLE statement

ALTER TABLE USERADDRESS ADD CONSTRAINT FK_USERID_USERADDRESS FOREIGN KEY (UserId) REFERENCES users(user_id)

Drop a Foreign key

ALTER TABLE USERADDRESS DROP FK_USERID_USERADDRESS

CHECK Constraint

CHECK Constraint is used to check the inserted or updated value to the column satisfy any specific condition. It will help to increase the data accuracy.
Example
CHECK on CREATE TABLE Statement

CREATE TABLE users
(
	user_id bigint IDENTITY(1,1) NOT NULL,
	username [varchar](45) NOT NULL,
	password [varchar](45) NOT NULL,
	email [varchar](45) NOT NULL,
	Age int check (Age >= 18),
	PRIMARY KEY (user_id ASC)
)

CHECK on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT CK_USERAGE CHECK (Age >= 18)

Drop a CHECK Constraint

ALTER TABLE users DROP CK_USERAGE

DEFAULT Constraint

DEFAULT Constraint is used to set a default value to a column in a table. When an INSERT statement is not inserting value to DEFAULT Constraint column, then the default value will be inserted into that column.
Example
DEFAULT on CREATE TABLE statement

CREATE TABLE [dbo].[users]
(
	user_id bigint IDENTITY(1,1) NOT NULL,
	username [varchar](45) NOT NULL,
	password [varchar](45) NOT NULL DEFAULT 'Admin',
	email [varchar](45) NOT NULL,
	Age int,
	PRIMARY KEY (user_id ASC)
)

DEFAULT on ALTER TABLE statement

ALTER TABLE users ADD CONSTRAINT PWD_DEF DEFAULT 'Admin' for [password]

Drop a DEFAULT Constraint

ALTER TABLE users DROP PWD_DEF