SQL Server Constraints

Constraints : Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table.Constraints can be defined in two ways:

Column Level : The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.

Table Level : The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints :

Primary Key Constraints : Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.

Create Table Statement  to create Primary Key :

1. Column Level

USE AdventureWorks2008

GO

CREATE TABLE Products

(

ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,

ProductName VARCHAR(25)

);

GO

2. Table Level

CREATE TABLE Products

(

ProductID INT,

ProductName VARCHAR(25)

CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

);

GO

Alter Table Statement to create Primary Key :

ALTER TABLE Products

ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

GO

Alter Statement to Drop Primary key:

ALTER TABLE Products

DROP CONSTRAINT pk_products_pid;

GO

Foreign Key Constraints : Foreign Key is a field in database table that is Primary key in another table. It can accept multiple nulls, duplicate values.

Create Table Statement  to create Foreign Key :

1. Column Level :

USE AdventureWorks2008

GO

CREATE TABLE ProductSales

(

SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,

ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),

SalesPerson VARCHAR(25)

);

GO

2. Table Level :

CREATE TABLE ProductSales

(

SalesID INT,

ProductID INT,

SalesPerson VARCHAR(25)

CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),

CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)

);

GO

Alter Table Statement to create Foreign Key :

ALTER TABLE ProductSales

ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)

GO

Alter Table Statement to Drop Foreign Key :

ALTER TABLE ProductSales

DROP CONSTRAINT fk_productSales_pid;

GO

UNIQUE Constraint : The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Create Table Statement  to create UNIQUE constraint:

CREATE TABLE Persons

(

P_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

Alter Table Statement to create UNIQUE constraint:

ALTER TABLE Persons

ADD UNIQUE (P_Id)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

Alter Table Statement to Drop UNIQUE constraint:

ALTER TABLE Persons

DROP CONSTRAINT uc_PersonID

NOT NULL Constraint : The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. By default, a column can hold NULL.

Example Using NOT NULL Constraint :

Create table student(s_id int NOT NULL,Name varchar(60),age int);

The above query will declare that the s_id field of student table will not take NULL value.

An attempt to execute the following SQL statement,

INSERT INTO student (Name, age) VALUES ('Smith', 25);

will result in an error because this will lead to column "S_ID" being NULL, which violates the NOT NULL constraint on that column.

Following example demonstrates both the way to create NOT NULL constraints.

USE AdventureWorks

GO

-- NOT NULL Constraint when Table is created

CREATE TABLE ConstraintTable

(ID INT, ColSecond INT NOT NULL)

GO

-- NOT NULL Constraint after Table is created

ALTER TABLE ConstraintTable

ALTER COLUMN ID INT NOT NULL

GO

--Clean Up

DROP TABLE ConstraintTable

GO

CHECK Constraint : The CHECK constraint is used to limit the value range that can be placed in a column.

Default Constraint: Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.

Create Table Statement to create Default Constraint :

1. Column Level

USE AdventureWorks2008

GO

CREATE TABLE Customer

(

CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY,

CustomerName VARCHAR(30),

CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN'

);

GO

2. Table Level : Not applicable for Default Constraint

Alter Table Statement to Add Default Constraint :

ALTER TABLE Customer

ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress

GO

Alter Table to Drop Default Constraint :

ALTER TABLE Customer

DROP CONSTRAINT df_customer_Add

GO

Example:

CREATE TABLE Student (Student_ID integer Unique, Last_Name varchar (30), First_Name varchar (30),

Score Integer DEFAULT 80);

and execute the following SQL statement,

INSERT INTO Student (Student_ID, Last_Name, First_Name) VALUES (10, 'Johnson', 'Rick');

Output :

The table will look like the following:

Student_ID         Last_Name         First_Name        Score

10                       Johnson                 Rick                         80