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 :
- Column Level
USE
AdventureWorks2008
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT
pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
- 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 :
- 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
- 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.
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 :
- 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
- 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
