Constraints in SQL Server.

Hi Friends, in this post I would like to explain Constraints in SQL Server.

* Constraint is a mechanism which can be activated automatically when the specified event is occurred.

* Constraint is a role it can be defined on the selected columns in order to prevent invalid data.

* It is one of the data integrity concepts to enforce integrity explicitly.

Different types of constraints:

1) Not Null: It does not allow null values in the specified column.

2) Check: It is used to validating user conditions.

3) Unique: It doesn’t allow duplicate values in the specified column & it allows only single NULL value.

4) Primary Key: It doesn’t allow duplicate & NULL values.

5) Foreign Key: This is for establishing relation between 2 tables. One table will act as Parent & another will act as Child. And it is associated with either Primary key (or) Unique Constraints.

* For establishing the relation between the tables should maintain at least one common column.

* Foreign key allows duplicate & Null values.

* Only one Primary Key allowed for table.

* Maximum 253 Foreign Keys allowed for table.

* We can define only Primary Key/Unique Key on a single column.

6) Default: It is for defining user default values instead of storing system default values.

Syntax for defining constraints:

Create table tableName

(Column1 datatype Constraint1 Constraint2 ....,

Column2 datatype Constraint1 Constraint2 ....,

.........)

For Example:

Create table Department

(DeptId int PrimaryKey,

DeptName varchar(20) Unique NotNull,

Location varchar(20) Default ‘Hyderabad’)

Thank You…