Pages

Monday 20 June 2011

Constraints in SQL Server.

1 comments
 

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…

Shout it

One Response so far.

  1. Memtech says:

    This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.

    http://mindstick.com/Articles/d1eb79d6-8b7c-43b6-9aef-73c6e2e909a1/?SQL%20Constraints

    Thanks

Leave a Reply