Constraints
are used to limit the type of data that can go into a table.
Constraints
can be specified when a table is created (with
the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
Here are the
most important constraints:
·
PRIMARY
KEY
·
NOT
NULL
·
UNIQUE
·
FOREIGN
KEY
·
CHECK
·
DEFAULT
·
IDENTITY
PRIMARY KEY
The PRIMARY KEY constraint uniquely
identifies each record in a database table.
Primary keys must contain unique values. It is normal to just use running numbers, like 1, 2, 3, 4, 5, … as values in Primary Key column. It is a good idea to let the system handle this for you by specifying that the Primary Key should be set to identity(1,1). IDENTITY(1,1) means the first value will be 1 and then it will increment by 1.
Each table should have a primary key, and each
table can have only ONE primary key.
If we take a closer look at the EMPLOYEE
table created earlier:
----------------------------------------------------------------------------------------
CREATE TABLE Employee
(
Id int identity (1,1),
Name varchar(50) NOT NULL,
JoinDate DateTime
NOT NULL,
LeaveDate DateTime,
DateOfBirth DateTime
NOT NULL,
CONSTRAINT PK_Employee_Id primary key (Id)
)
The constraint is used to add constraint into table. It’s basic structure
is CONSTRAINT <name of COnstriant> <type of Constraint> and
(<field name of constraint>)
-------------------------------------------------------------------
As you see we use the “Primary Key” keyword to specify that a
column should be the Primary Key.
Setting Primary Keys in the Designer
Tools:
If you use the Designer tools in SQL Server you can easily set the primary Key in a table just by right-click and select “Set primary Key”.
The primary Key column will then have a small key in front to illustrate that this column is a Primary Key.
FOREIGN KEY
A FOREIGN KEY in one table
points to a PRIMARY KEY in another table.
Example:
Example: Create
the table Employee, Department and EmpDept
-----------------------------------------------------
CREATE TABLE Department
(
Id int identity (1,1),
Code
varchar(20) NOT NULL,
Name varchar(50) NOT NULL,
CONSTRAINT PK_Department_Id primary key (Id)
)
---------------------------------------------------------------------------------------
CREATE TABLE Employee
(
Id int identity (1,1),
Name varchar(50) NOT NULL,
JoinDate
DateTime NOT NULL,
LeaveDate
DateTime,
DateOfBirth
DateTime NOT NULL,
CONSTRAINT PK_Employee_Id primary key (Id)
)
-----------------------------------------------------------------------------------------
CREATE TABLE EmpDept
(
Id int identity (1,1),
EmployeeId
int,
DepartmentId
int,
JoinDate
DateTime NOT NULL,
LeaveDate
DateTime,
CONSTRAINT PK_EmpDept_Id primary key (Id),
CONSTRAINT FK_Employee_Id
FOREIGN KEY
(EmployeeId) REFERENCES Employee,
CONSTRAINT FK_Department_Id
FOREIGN KEY
(DepartmentId) REFERENCES Department
)
--------------------------------------------------------------------
The FOREIGN KEY constraint is
used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data
from being inserted into the foreign key column, because it has to be one of
the values contained in the table it points to.
Setting Foreign Keys in the
Designer Tools:
If you want to use the designer, right-click on the column
that you want to be the Foreign Key and select “Relationships…”:
The following window pops up (Foreign Key Relationships):
Click on the “Add” button and then
click on the small “…” button. Then the following window pops up (Tables and
Columns):
Here you specify the primary Key Column in the Primary Key
table and the Foreign Key Column in the Foreign Key table.
NOT NULL
The NOT NULL
constraint enforces a column to NOT accept NULL values.
If we take a closer look at the
EMPLOYEE table created earlier:
----------------------------------------------------------------------------------------------------
CREATE TABLE Employee
(
Id int identity (1,1),
Name varchar(50) NOT NULL,
JoinDate DateTime
NOT NULL,
LeaveDate DateTime,
DateOfBirth DateTime
NOT NULL,
CONSTRAINT PK_Employee_Id primary key (Id)
)
--------------------------------------------------------------------------------------------------------
We see that “Name”, “JoinDate” and “DateOfBirth” is set to
“NOT NULL”, this means these columns needs to contain data. While “LeaveDate” may
be left empty, i.e, they don’t need to filled out.
Note! A primary key column cannot contain NULL values.
Setting NULL/NOT NULL in the
Designer Tools:
In
the Table Designer you can easily set which columns that should allow NULL or
not:
UNIQUE
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.
A PRIMARY KEY constraint
automatically has a UNIQUE constraint defined on it.
Note! You can have many UNIQUE
constraints per table, but only one PRIMARY KEY constraint per table.
If we
take a closer look at the DEPARTMENT table created earlier:
----------------------------------------------------------------------------------------------------------
CREATE TABLE Department
(
Id int identity (1,1),
Code
varchar(20) NOT NULL UNIQUE,
Name varchar(50) NOT NULL,
CONSTRAINT PK_Department_Id primary key (Id)
)
-------------------------------------------------------------------------------------------------------------
Setting UNIQUE in the
Designer Tools:
If you want to use the designer,
right-click on the column that you want to be UNIQUE and select “Indexes/Keys…”:
Then click
“Add” and then set the “Is Unique” property to “Yes”:
CHECK
The CHECK constraint is used to limit the value range that
can be placed in a column.
If we
take a closer look at the DEPARTMENT table created earlier:
---------------------------------------------------------------------------------------------------
CREATE TABLE Department
(
Id int identity (1,1),
Code varchar(20) NOT NULL UNIQUE
CHECK(Code>0),
Name varchar(50) NOT NULL,
CONSTRAINT PK_Department_Id primary key (Id)
)
-----------------------------------------------------------------------------------
In this case, when we try to insert a Customer Number less than zero
we will get an error message.
Setting
CHECK constraints in the Designer Tools:
If you want
to use the designer, right-click on the column where you want to set the
constraints and select “Check Constraints…”:
Then click “Add”
and then click “…” in order to open the Expression window:
In the Expression window you can type in the
expression you want to use:
DEFAULT
The DEFAULT constraint is
used to insert a default value into a column.
The default value will be
added to all new records, if no other value is specified.
If we take a closer look
at the EMPLOYEE table created earlier:
-----------------------------------------------------------------------------------
CREATE TABLE Employee
(
Id int identity (1,1),
Name varchar(50) NOT NULL,
JoinDate
DateTime NOT NULL,
LeaveDate
DateTime,
DateOfBirth
DateTime NOT NULL,
Country varchar(20)
DEFAULT ‘India’,
CONSTRAINT PK_Employee_Id primary key (Id)
)
-----------------------------------------------------------------------------------
Setting DEFAULT values in the Designer Tools:
Select the column
and go into the “Column Properties”:
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
As shown below, we use the IDENTITY() for this. IDENTITY(1,1) means the first value will be 1 and then it will increment by 1.
