Constraints in C#

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

1. Declarative integrity Constraints
2. Procedure integrity constraints.

Lets learn in details

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

1. 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.
   
   
2. 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’
   
   
3. 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”.
   
   
4. 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.
   
   
5. 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.
   
   
6. 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.