In this article I
demonstration on constraints in c#.
Constraints:
Constraints can be defind as the condition
specifies on coloum to maintain data integrity with the database.
Constraints can be classified into
two type
1)Declerative integrity Constraints
2)Procedure integrity constraints.
Lets learn in details,
1)declerative Intergrity constraints:-
The constraints that
can be specify while creating a table.
The declerative intergrity constrates can be specified either as
a)Coloum Level
b)Table Level.
a)Coloum Level:-
When we want to specify the
constraints on a single coloum then you can specify either at coloum level or
Table Level.
b)Table Level:-
When we want to specify the
constraints on a combination of multiple
coloum then you can specify Table
Level.
2)Procedure Intergrity constraints:-
The constraints that
cannot be specify while creating a
table.and can be specify by using
trigger.
The declerative constrat avaliable in sql server are as follows
1)NOT NULL :-
This constraints is used for not
allowing null into the coloum when you want to insert a row in to the table.
NOT NULL can be specify only at
coloumn level and it cannot be specify at table level.
Syntax:
<Coloum Name>
<Datatype> NOT NULL.
2)Default:
Default constraints is used to
specify a default value for that columns which will be automatically inserted
to that coloum when user insert row to that table without providing values for that colums.
Syntax:
<Coloum Name> <Data
Type>
Default <Default Value>.
Eg:
Subject varchar[50]
Default ‘cpp’
3)UNIQUE:-
Unique constraints is used for not allowing duplicate values in to the coloum.
UNIQUE constraints can be coloum
level and Table Level also.
The following syntax is used for
coloum level .
<Coloum
Name> <Data Type>
UNIQUE
[clustred/nonclustered].
The following syntax is used for
Table level .
UNIQUE <Coloum Name>
[clustred/nonclustered].
When you specify unique constraint on a coloum then index is automatically created on that coloum
which is by default “ Non Clusterd”.
4)Primary Key :-
Primary key constaints 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].
Eg:
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.
5)Foregian Key or Reference Intergrity Constraints :-
Foregian key constraints is used to restrict the user from inserting only those values that are avaliable in reference coloumn.foregian key must have a colum has refereces so it is also called as “refernce intergrity constraints”.foregian key allows null .a coloumn to be referred by foregian key must be primary key or unique key.
Coloum level :-
<Coloumn name><datatype>
References
<table Name>(ColoumnName)
[on UPDATE no action/cascade/set
Null/Set Default]
[on Delete no action/cascade/set
Null/Set Default]
Eg:
Sid int foregian key sid references mark sid
Table level :-
Foregian
key (<Coloumn List>)
References
<table Name>(ColoumnList)
[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 foregian key values when corrsoponding
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 coloum value will automatically update
the corrosponding foregian key coloum value and delete a row from primary key
table will automatically delete all the rows from foregian key table that
referes to that .
III) when we set the option as SET NULL then updating and deleting the primary key value will set corrosponding foregian key coloum value to NULL.
IV)when these option are set to SET DEFAULT then updation the primary key column values or deleting row from primary key table will automatically set corrosponding foregian key column values to by default values avaliable in foregian key coloumn.
If there is no default values on column then it is set to NULL.
6)CHECK Constraints:-
Check constraints is used to specify your own
conditon to check on a coloumn.
Coloum Level:-
<Coloum
Name><Data Type>
Check
[<Condition>]
Eg
C int
Check (c<=0 and c>=100)
Table Level:-
[Constraint <Constrant Name>]
Check
[<Condition>]
if any problem occour frankly leave mail to me dotnetbyabhipatil@gmail.com or for more article visit my blog dotnetbyabhipatil.blogspot.in
your feedback and suggestion is always welcome for me.
Abhijit Patil is a Founder of Dotnetbyabhipatil ,he Passionate on Technology ,He have 3 Award on dotnetspider Including Golden. He got 2nd Time MVP(Most Valuable Professional) award at Delhi International Conference on C# Corner. also Chapter Lead of C# Corner Pune Chapter.
ConversionConversion EmoticonEmoticon