Constraint in Sql Server

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    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.