Constraints in a
database maintain the integrity of the database.
RDBMS Stands for
(Relational dataBase management System)
Constraints enable the RDBMS enforce the integrity of the
database automatically, without needing you to create triggers, rule or
defaults.
Types of constraints:
--> PRIMARY KEY
--> UNIQUE
-->FOREIGN KEY
--> CHECK
--> NOT NULL
A PRIMARY KEY constraint is a unique identifier
for a row within a database table. Every table should have a primary key
constraint to uniquely identify each row and only one primary key constraint
can be created for each table. The primary key constraints are used to enforce
entity integrity.
A UNIQUE
constraint enforces the uniqueness of the values in a set of columns, so no
duplicate values are entered. The unique key constraints are used to enforce
entity integrity as the primary key constraints.
A FOREIGN KEY constraint prevents any actions
that would destroy link between tables with the corresponding data values. A
foreign key in one table points to a primary key in another table. Foreign keys
prevent actions that would leave rows with foreign key values when there are no
primary keys with that value. The foreign key constraints are used to enforce
referential integrity.
A CHECK
constraint is used to limit the values that can be placed in a column. The
check constraints are used to enforce domain integrity.
A NOT NULL constraint enforces that the
column will not accept null values. The not null constraints are used to
enforce domain integrity, as the check constraints.
You can create constraints when the table is created, as
part of the table definition by using the CREATE
TABLE statement
Example
CREATE TABLE employee(
EmployeeId INT NOT
NULL,
LName VARCHAR(30)
NOT NULL,
FName VARCHAR(30)
NOT NULL,
Address
VARCHAR(100) NOT NULL,
HireDate DATETIME
NOT NULL,
Salary MONEY NOT
NULL CONSTRAINT check_sale CHECK (salary > 0)
)
ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)
ALTER TABLE employee
DROP CONSTRAINT pk_employee
Types of constraints
I focus on four types of constraints: primary key, foreign
key, unique, and check. Here's a brief overview of each.
Primary key
This constraint is used to guarantee that a column or set of
columns on a table contain unique values for every record in the given table.
This lets you ensure data integrity by always being able to uniquely identify
the record in the table.
Get SQL tips in your
inbox
TechRepublic's SQL Server newsletter, delivered each
Tuesday, contains hands-on tips that will help you become more adept with this
powerful relational database management system.
Automatically sign up today!
A table can have only one primary key constraint defined on
it, and the rows in the primary key columns cannot contain null values. A
primary key constraint can be defined when a table is created, or it can be
added later.
This script creates a primary key constraint on a single
field when the table is created:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
GO
CREATE TABLE
[dbo].[SalesHistory](
[SaleID] [int]
IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product]
[char](150) NULL,
[SaleDate]
[datetime] NULL,
[SalePrice]
[money] NULL
)
GO
The followings script creates the primary key constraint
when the table is created. This method allows you to define a name for the
constraint and to create the constraint on multiple columns if necessary.
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE SalesHistory(
SaleID int
IDENTITY(1,1) NOT NULL,
Product char(150)
NULL,
SaleDate
datetime NULL,
SalePrice money
NULL,
CONSTRAINT
pk_SaleID PRIMARY KEY (SaleID)
)
GO
This script creates the primary key constraint on the table
after it is created:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
GO
CREATE TABLE
SalesHistory(
SaleID int IDENTITY(1,1) NOT NULL,
Product char(150) NULL,
SaleDate datetime NULL,
SalePrice money NULL
)
GO
ALTER TABLE
SalesHistory
ADD CONSTRAINT
pk_SaleID PRIMARY KEY (SaleID)
GO
Foreign key
This constraint limits the values of columns in one table
based upon the values of columns in another table. This link between the two
tables requires the use of a "lookup table," which contains the
accepted list of values; this list must contain a unique or primary key
constraint. After the constraint is established between the two tables, any
data modifications to the fields defined in the constraint on the foreign key
table will cause a validation to ensure that the data being updated or inserted
is contained in the lookup table.
The script in Listing A creates a ProductTypes table, which
will serve as the lookup table and the SalesHistory table, which will reference
the ProductID in the ProductTypes table. If I had excluded the constraint
definition in the table declaration, I could go back later and add it. You can
do this with the script in Listing B.
The previous script contains the WITH NOCHECK clause. I use
it so that any existing values in the table are not considered when the
constraint is added. Any records in the table that violate the newly added
constraint will be ignored so that the constraint is created. The constraint
will only be applicable to new records entered into the SalesHistory table.
Unique
This constraint guarantees that the values in a column or
set of columns are unique. Unique and primary key constraints are somewhat
similar because each provide a guarantee for uniqueness for a column or set of
columns. A primary key constraint automatically has a unique constraint defined
on it.
There are two differences between the constraints: (1) You
may have only one primary key constraint per table, yet you may have many
unique constraints per table; (2) A primary key constraint will not allow null
values but a unique constraint will (although it will only allow one null value
per field).
This script creates a unique constraint on the SaleID column
when the table is created:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory](
[SaleID] [int]
NOT NULL UNIQUE,
[Product]
[char](150) NULL,
[SaleDate]
[datetime] NULL,
[SalePrice]
[money] NULL
)
GO
The following script creates a unique constraint on the
table at creation, and it allows for constraint naming and for defining the
unique constraint on multiple columns if necessary.
IF OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
GO
CREATE TABLE
[dbo].[SalesHistory](
[SaleID]
[int] NOT NULL,
[Product]
[char](150) NULL,
[SaleDate]
[datetime] NULL,
[SalePrice]
[money] NULL,
CONSTRAINT
uc_SaleID UNIQUE (SaleID)
)
GO
This script creates the unique constraint on the
SalesHistory table by altering the table after it has been created:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
GO
CREATE TABLE
[dbo].[SalesHistory](
[SaleID] [int]
NOT NULL,
[Product]
[char](150) NULL,
[SaleDate]
[datetime] NULL,
[SalePrice]
[money] NULL
)
GO
ALTER TABLE
SalesHistory
ADD CONSTRAINT
uc_SaleID UNIQUE(SaleID)
GO
Check
This constraint limits the value range, or domain, in a
column. Check constraints check the acceptable values against a logical
expression defined in the constraint. These constraints are similar to foreign
key constraints in that they both govern the acceptable values for a column or
set of columns in a given row in a table. You can create a check constraint at
the column or table level. A check constraint on a single column allows only
certain values for those columns, while a table check constraint can limit
values in certain columns based on values in other fields in the row.
The following script creates a check constraint on the
SalePrice column in the SalesHistory table, limiting entries where the
SalePrice must be greater than 4. Any attempt to enter a record with the
SalePrice present and less than 4 will result in an error.
IF OBJECT_ID('SalesHistory')>0
DROP TABLE
SalesHistory;
GO
CREATE TABLE
[dbo].[SalesHistory](
[SaleID]
[int] NOT NULL,
[Product]
[char](150) NULL,
[SaleDate]
[datetime] NULL,
[SalePrice]
[money] NULL CHECK (SalePrice > 4)
)
GO