SQL Server – Table Constraints

Table Constraints

We always want data in our database clean and in healthy state. To achieve this we need to use constraints. A constraint is high speed data validation check or business-logic check performed by database-engine level. There five type of constrains provided by SQL Server. We shall see each one of then in details.

1. Primary Key Constraint

A primary key is used to uniquely identify each row in a table. A primary key can consist of one or more then more columns in table. When they consist of more then one column they are called composite key. It ensures unique non-null key.

Example

— Create table Department with DeptID as Primary Key

CREATE TABLE Department(

DeptID INT PRIMARY KEY,

DeptName VARCHAR(25)

)

— OR make DeptID of existing table Department as Primary Key

ALTER TABLE Department ADD PRIMARY KEY (DeptID)

2. Foreign Key Constraint

A foreign key is a field in a table that matches the primary key column of another table. It establishes and enforces a link between data of two tables. It ensures that value points to a valid key.

Example

— Create table Employee with DeptID as Foreign Key

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT FOREIGN KEY REFERENCES Department(DeptID),

Salary INT,

ProjectCode INT,

)

— OR make DeptID as Foreign Key in exiting table Employee

ALTER TABLE Employee ADD CONSTRAINT DeptID_FK

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

3. Unique Constraint

It is similar to Primary Key Constraint. It ensures that every value in the column is unique value. It allows one NULL value.

Example

— Create table Employee with ProjectCode as Unique

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT,

Salary INT,

ProjectCode INT UNIQUE,

)

— OR make ProjectCode of existing table Employee as Unique

ALTER TABLE Employee ADD UNIQUE (ProjectCode)

4. Nullability Constraint

It defines whether a column will accept a NULL value or not. It is referred to as nullability of the column.

Example

— Create table Employee with FirstName as NOT NULL

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50),

DeptID INT,

Salary INT,

ProjectCode INT

)

— OR make FirstName of existing table Employee as NOT NULL

ALTER TABLE Employee ALTER COLUMN FirstName VARCHAR(50) NOT NULL;

5. Check Constraint

Check Constraint is extensively used to enforcement of general data-validation rules or simple business rules. Such as correct date range, max value that can be inserted into a table, etc.

Example

— Create table Employee with chk_salary constraint

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT,

Salary INT,

ProjectCode INT,

CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

)

— OR add constraint chk_salary on existing table Employee

ALTER TABLE Employee

ADD CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

We can add all five constraints while creating the table also. See the example below:

Example

— Create table Employee with all 5 constraints

CREATE TABLE Employee(

EmpID INT PRIMARY KEY,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50),

DeptID INT FOREIGN KEY REFERENCES Department(DeptID),

Salary INT,

ProjectCode INT UNIQUE,

CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

)

Good Luck!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: