Constraints
Constraints can be defined as the condition specifies on column to maintain data integrity with the database.
Constraints can be classified into two type
- Declarative integrity Constraints
- Procedure integrity constraints.
Lets learn in details
- Declarative Integrity constraints
The constraints that can be specify while creating a table.
The declarative integrity constraints can be specified either as
- Column Level
- Table Level.
Column Level
When we want to specify the constraints on a single column then you can specify either at column level or Table Level.
Table Level
When we want to specify the constraints on a combination of multiple column then you can specify Table Level.
- Procedure Integrity constraints
The constraints that cannot be specify while creating a table and can be specify by using trigger.
The declarative constraints available in sql server are as follows
- NOT NULL
This constraints is used for not allowing null into the column when you want to insert a row in to the table.
NOT NULL can be specify only at column level and it cannot be specify at table level.
Syntax
<Column Name> <Datatype> NOT NULL.
- Default
Default constraints is used to specify a default value for that columns which will be automatically inserted to that column when user insert row to that table without providing values for that columns.
Syntax
<Column Name> <Data Type>
Default <Default Value>.
Eg:
Subject varchar[50]
Default ‘cpp’
- UNIQUE
Unique constraints is used for not allowing duplicate values in to the column.
UNIQUE constraints can be column level and Table Level also.
The following syntax is used for column level.
<Column Name> <Data Type>
UNIQUE [clustred/nonclustered].
The following syntax is used for Table level.
UNIQUE <Column Name>
[clustred/nonclustered].
When you specify unique constraint on a column then index is automatically created on that column which is by default “ Non Clustered”.
- Primary Key
Primary key constraint will not allow null and duplicate value ,you can specify only one primary key for table.
If you want to give primary key for columns level then use the following syntax
<Column Name><DataType>
<Primary key>[Clustered/Non Clustered].
Example
Sid int primary key
If you want to give primary key for Table level then use the following syntax
<Primary key><Column Name>
[Clustered/Non Clustered].
Primary key constraints also create an index automatically but it is by default Clustered.
- Foreign Key or Reference Integrity Constraints
Foreign key constraints is used to restrict the user from inserting only those values that are available in reference column. foreign key must have a column has references so it is also called as “refernce integrity constraints ”.foreign key allows null .a column to be referred by foreign key must be primary key or unique key.
Column level
<Column name><datatype>
References <table Name>(ColumnName)
[on UPDATE no action/cascade/set Null/Set Default]
[on Delete no action/cascade/set Null/Set Default]
Example
Sid int references mark sid
Table level
Foreign key (<Column List>)
References <table Name>(ColumnList)
[on UPDATE no action/cascade/set Null/Set Default]
[on Delete no action/cascade/set Null/Set Default]
I) ON UPDATE/ON DELETE option are used to specify the action to take on foreign key values when corresponding primary key value is updated or rows are deleted from primary key by default no action is set.
II) When the option is set to CASCADE then updating the primary key column value will automatically update the corresponding foreign key column value and delete a row from primary key table will automatically delete all the rows from foreign key table that refers to that.
III) When we set the option as SET NULL then updating and deleting the primary key value will set corresponding foreign key column value to NULL.
IV) When these option are set to SET DEFAULT then updating the primary key column values or deleting row from primary key table will automatically set corresponding foreign key column values to by default values available in foreign key column.
If there is no default values on column then it is set to NULL.
- CHECK Constraints
Check constraints is used to specify your own condition to check on a column.
Column Level
<Column Name><Data Type>
Check [<Condition>]
Example
C int
Check (c<=0 and c>=100)
Table Level
[Constraint <Constrant Name>]
Check [<Condition>]
Your feedback and suggestion is always welcome for me.