difference between unique and primary key constraints

PRIMARY KEY constraint and UNIQUE constraint, both enforce uniqueness on applied columns. A primary key constraint can not be created on a column that accepts NULL value.

If a column is set to accept NULL a primary key can not be created. In easy words if you define a column as Primary key then the column must have NOT NULL value for each row.

SQL Server also creates clustered index by default on primary keys, on unique keys non-clustered index is created by default.

A table can have only one unique index and 249 non-unique indexes.

From Microsoft : Microsoft® SQL Server™ automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints. Unless a clustered index already exists on the table or a nonclustered index is explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

 



 3/17/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.