In this tutorial we are going to learn different types of constraint available in SQL server.
Constraints are nothing but the condition specified on column in table to maintain data integrity in database.Constraints are classified in two types:
1. Declarative integrity constraints
2. Procedural integrity constraints.
Declarative integrity constraints are constraints that are specified while creating the table.
Procedural integrity constraints are constraints that can't be specified while creating the table and can be specified using Triggers. Declarative integrity constraints can be specified either at column level or table level.
Column level Constraints:
These types of constraints is given when the table is created.
Create Table My_Constraint
(
ID int NOT NULL,
Salary int CHECK(Salary>15000)
)
Table Level Constraints:
These types of constraints is given after the creation of the table using the Alter Command.
Alter Table My_Table
Add constraint Check_Constraint Check(Salary>5000)
Declarative integrity constraints available in SQL server are as follows:
1. Not Null
Purpose of Not Null constraints is not to allow null entries into a column. When you insert a row into the table without providing value for column then by default it will get set to null.
Column Level:
CREATE TABLE Table_Name
(
Column_Name Datatype [Constraint <Constraint_Name>] NOT NULL,
);
Table Level:
ALTER TABLE Table_Name
ALTER COLUMN Column_Name Datatype NOT NULL
2. Default
Default constraint is used to provide default value for a column which will be automatically inserted to the column when you insert a row without providing a value for that column.
Column Level:
Create Table Table_Name
(
Column_Name DataType [Constraint <Constraint_Name>] Default(Value)
)
Table Level:
Alter Table Table_Name
Add Constraint Constraint_Name Default(Value) for[Column_Name]
3. Unique
Unique constraint is used to not allowing the users to insert duplicate values into a column. When you specify unique constraint on a column then index is automatically created on that column which is by default Non clustered index.
Column Level:
Create Table Table_Name
(
Column_Name Datatype [Constraint <Constraint_Name>] Unique
)
Table Level:
Alter Table_Name
Add Constraint Constraint_Name Unique(Column_Name)
4. Primary Key
This is very important among all constraints. Before moving to the definition of primary key we will see some more keys which is important to understand the primary key constraint.
a. Super Key
A single column or combination of multiple columns that can uniquely identifies a row in a table is called as Super key. If we take a table department with the columns deptno, deptname, city as example then the following is the list of super keys in that table.
{deptno}
{deptname}
{deptno, deptname}
{deptno, city}
{deptname, city}
{deptno, deptname, city}
b. Candidate Key
Among the available super keys the super key with minimum number of column is called as Candidate key.
c. Primary key or secondary key
Among all available candidate keys the candidate key which we choose to uniquely identify the rows is called as Primary key and remaining is called as Secondary or alternate keys. In our example of department table if we choose deptno for our purpose then we will say deptno as primary key and deptname will become secondary key or alternate key.
Column Level:
Create Table Table_Name
(
Column_Name Datatype [Constraint <Constraint_Name>] Primary Key
)
Table Level:
Alter Table Table_Name
Add constraint Constraint_Name Primary Key(Column_Name)
5. Foreign Key
Foreign key constraint is used to restrict the users from inserting only those value that exist in another column.While giving foreign key constraints on a column you must provide a column as a reference to it.The foreign key will allow only those values that exist in the reference column.
Foreign key constraint allow NULL. A column to be referred by foreign key must have either Primary key or unique constraint.
Column Level:
Create Table Table_Name
(
Column_Name Datatype [Constraint <Constraint_Name>] References Reference_Table_Name(Reference_Column_Name)
)
Table Level:
ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name FOREIGN KEY(Column_Name)
REFERENCES Reference_Table (Column_Name)
6. Check
Check constraint is used to specify your own conditions to check on a column.
Column Level:
Create Table Table_Name
(
Column_Name Datatype Constraint Constraint_Name Check(Condition)
)
Table Level:
Alter Table Table_Name
Add Constraint Constraint_Name Check(Condition)
While specifying constraints on column you can provide a name which is used to disable and enable the constraints also to delete the constraint, but providing a name to the constraint is optional because when you are not providing a name then SQL server will automatically assign a name to the constraint.
Hope you will understand the concept of constraints in SQL server.
