#DataIntegrityInSQLSERVER #ConstraintsinSQLSERVER #MSSQLSERVERTutorials

#CONSTRAINTS:
SQL Constraints is known as rules. It used to limit the type of Data that can go into a table.
Constarints are used to maintain the accuracy and integrity of the data inside a table.
Before storing user supplied information into the table server performs data integrity process in order to verify whether user supplying valid information or not.
Constaints can be devided into two types
1. Column Level Constraint : Limits only Column Data.
2. Table Level Constraint : Limits whole table data
Below Constraints are applied on Column / table level.
#PRIMARYKEY
#UNIQUE
#NOTNULL
#DEFAULT
#CHECK
#FOREIGNKEY
#NULL

#PRIMARYKEY:
when we place a PRIMARY KEY constraint on any column then it will not accept duplicate values and it does not accept any null values, mean time the data in that column will be arranged in ascending order due to CLUSTERED INDEX.

It’s a combination of UNIQUE+ NOT NULL + CLUSTERED INDEX.

#UNIQUEKEY:
When we place UNIQUE constraint on any column ,It will not allow duplicate values but it allows single NULL value

#NOTNULL:
When we place NOT NULL constraint on any column, it will not allow any NULL values. Entering value for that column is mandatory

#CHECK:
It is used for evaluating range condition on numeric columns. It will check values provided for column.

#DEFAULT:
It is useful to provide default value into a column ,when user will not provide any value while inserting the data into the table then default value will be arranged.

#FOREIGNKEY:
It is used to establish a relation ship betwen the parent and child tables.
Foreign Key can accept duplicate values and Null values
Foreign Key has to take the values from its corresponding Parent table Primary Key.

SP_HELPCONSTRAINT:
This Stored Procedure is used to display the description of constraints which have been placed on different columns of a specific table.

ON DELETE CASCADE:
Without specifying the ON DELETE CASCADE it is not possible to delete the record in the PARENT table

If there are dependent records from the child table for that record.

ON DELETE CASCADE if used when the record in the PARENT table is deleted all the dependent records in the child table will be also be deleted.

ON UPDATE CASCADE:
With out specifying the ON UPDATE CASCADE it is not possible to update the record in the PARENT table if there are dependent records from the child table for that record.

ON UPDATE CASCADE if used when the record in the PARENT table is updated all the dependent records in the child table will be also be updated

#DATAINTEGRITY

Data Integrity means data validation or data checking process or Type Checking process. Before storing user supplied information into the table server performs data integrity process in order to verify whether user supplying valid information or not.

If user supplies valid information then only it will stored into the table otherwise server raises an error message like ‘Data Type Mismatch’.

We can achieve this Data Integrity in Three ways
1. Data Types
2. Constraints
3. Triggers

Constraints:Constraint is nothing but condition on column. If we perform any operation against to constraint server raises an error message.

OR

We can place constraints (with constraint names or with out constraint names) on columns while creating the table or after creating the table.

TYPES OF CONSTRAINTS:

1) Unique Constraint: When we place UNIQUE constraint on any column ,It will not allow duplicate values but it allows single null value

2) Not null Constraint: When we place NOT NULL constraint on any column, it will not allow any null values. Entering value for that column is mandatory.

3) Check Constraint: It is used for evaluating range condition on numeric columns. It will check values provided for column. Like salary should greater than 5000 and less than 40000

It is used for evaluating character comparison conditions on character columns

4) Primary Key Constraint:

It is a combination of UNIQUE+ NOT NULL + CLUSTERED INDEX.
It means when we place PRIMARY KEY constraint on any column then it will not accept any duplicate values and it does not accept any null values mean time the data in that column will be arranged in ascending order due to CLUSTERED INDEX.

5) Foreign key Constraint:

a. Foreign Key must be Primary Key
b. Foreign Key can accept duplicate values and Null values
c. Foreign Key has to take the values from its corresponding Primary Key.

6) Default Constraint:It is useful to provide default value into a column when user will not provide any value while inserting the data into the table then default value will be arranged.