SQL Server primary and unique keys

Question posted in Computer Software on 06 2010

Rate question difficulty level

1 Votes

what are the difference between the two? what are they used for?

Add an answer

Share |

10 Answers

Gadi Berqowitz

Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.

Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

06/24/2010

Guest

1)You can create only one PK on the table
2) By default PK, creates Clustered Unique index on the table , UNIQUE constraint creates inique NCI...
3) With UNIQUE constraint on the table you can have only one NULL value...

06/27/2010

Guest

A UNIQUE KEY can uniquely identify each row in a table. A UNIQUE KEY comprises a single column or a set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns if NULL values are not used. Depending on its design, a table may have arbitrarily many UNIQUE KEYS BUT at most one PRIMARY KEY.
A PRIMARY KEY is a special case of UNIQUE KEYS. The major difference is that for UNIQUE KEYS the implicit NOT NULL constraint is not automatically enforced, while for PRIMARY KEYS it is enforced. Thus, the values in UNIQUE KEY columns may or may not be NULL. Another difference is that PRIMARY KEYS must be defined using another syntax.
DEFINING PRIMARY KEYS

ALTER TABLE
ADD [ CONSTRAINT ]
PRIMARY KEY ( {, }... )

CREATE TABLE table_name (
id_col INT,
col2 CHARACTER VARYING(20),
...
CONSTRAINT tab_pk PRIMARY KEY(id_col),
...
)

CREATE TABLE table_name (
id_col INT PRIMARY KEY,
col2 CHARACTER VARYING(20),
...
)

DEFINING UNIQUE KEYS

ALTER TABLE

ADD [ CONSTRAINT ]
UNIQUE ( {, }... )

CREATE TABLE table_name (
id_col INT,
col2 CHARACTER VARYING(20),
key_col SMALLINT,
...
CONSTRAINT key_unique UNIQUE(key_col),
...
)

CREATE TABLE table_name (
id_col INT PRIMARY KEY,
col2 CHARACTER VARYING(20),
...
key_col SMALLINT UNIQUE,
...
)

06/27/2010

Guest

Both the keys enforces uniqueness in terms of values on which they are applied. However there is (are) a (some) difference(s):
PK does not accept null and creates clustered index and UK accepts one null value and creates non clustered indexes.

Kind Regards
PRatik Shah

06/28/2010

Guest

A primary key can be created using either a clustered or nonclustered index. In Microsoft SQL Server, clustered is the default, but this can, and often should, be changed.

06/28/2010

Joselito

Whoa!You made certain fine poitns there. Used to looking about them and located a number of persons will consent with the blog. This site is one of the best Ive witnessed in a little while. Your post offers excellent material. We've previously been browsing in many different places for info for this sort of stuff. I looked everywhere, I looked in Bing, and that i missed your posting right until today. Honestly, you truly present great articles, really it is actually informative. We're returning here in the forseeable future. You'll want to maintain website updated, it's superb. Go take a look at my website and obtain a Nook

04/30/2012

tnqsfn

EoEq4R dsigklzlugbz

05/02/2012

Bear

. Your preferred cause appeared to be at the internet the easiest aspect to consider of. I say to you, I certainly obtain irked even as folks consider issues that they just do not comprehend about. A person were able to hit the toe nail on the very best as well as described the entire factor without necessity side effect , folks might take an indication. Will likely be once again to obtain more. Thank you

auto insurance quotes auto insurance rates

05/12/2012

lala

Coach Outlet is an American leather goods company famous for its ladies handbags and purses. Coach Factory Brand may be creating Coach Bags styles which are absolutely multicolored and fun. Here, we talk about Coach Outlet Online comes in different colors and sizes, which means that you can go in for Coach Outlet Online one that suits you the most.

05/17/2012

Lisa

Many thanks man really nice site plus some excellent articles, keep up the greatI had been surfing search engines and I noticed your website which is quite interesting, keep it up! I simply added this blog website to my search engines readers, excellent things. I Cannot obtain sufficient!

ordering cialis online nexium

05/19/2012

Add an answer*

Your name