PRIMARY key versus UNIQUE index

Hi Group
Other than the fact that PRIMARY keys seem to be defined at table
creation time - what's the difference between a primary key and a
UNIQUE index? If there is a difference, what's the typical use of a
PRIMARY key?
TIA
MarkusPrimary keys do not allow null values ... that is the only difference betwee
n
Primary key and Unique Key.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Markus Zingg" wrote:

> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||and unique key allows just one null value.
"Vadivel" wrote:
> Primary keys do not allow null values ... that is the only difference betw
een
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||Hi
Not, the other difference is that when you create a primary key, SQL Server
creates a clustered index ,as opposite UNIQUE key it creates unique
nonclustered index
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
> Primary keys do not allow null values ... that is the only difference
> between
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||From the database design point of view the PRIMARY KEY should reflect the
natural key of your data.
From the technical point of view, a PRIMARY KEY does not allow NULL's, where
as a UNIQUE Constraint / Index allows at least 1 NULL.
From my own stand point I use PRIMARY KEY constraints on the natural key and
a UNIQUE Constraint for the surrogate key eg...
CREATE TABLE currency (
id int not null identity constraint sk_currency unique clustered,
code char(3) not null constraint pk_currency primary key nonclustered
)
CREATE TABLE trade (
..
...
settlement_currency_id int not null references currency( id ),
trade_currency_id int not null references currency( id ),
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Markus Zingg" <m.zingg@.nct.ch> wrote in message
news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||Unless it's composite.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:01F43F78-1A63-4AD9-B5C0-D4F2486524C1@.microsoft.com...
> and unique key allows just one null value.
> "Vadivel" wrote:
>|||Just for the sake of completion. We can create Primary Keyws without
clustered index also. Check out
http://vadivel.blogspot.com/2006/03...el.blogspot.com
"Uri Dimant" wrote:

> Hi
> Not, the other difference is that when you create a primary key, SQL Serve
r
> creates a clustered index ,as opposite UNIQUE key it creates unique
> nonclustered index
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
>
>|||This is a default behavior for the creation of a primary key; you can
override it, and make your underlying index a nonclustered one.
CREATE TABLE foo (pkid int PRIMARY KEY NONCLUSTERED,
doe smalldatetime)
CREATE CLUSTERED INDEX clidx ON foo(doe)
I try to keep the concepts of indexes and constraints as seperate as
possible in my mind (and in my designs); one is a physical tool to
assist in database performance, the other is a logical tool to assist
in data integrity. Sometimes it's useful to have a clustered primary
key; sometimes it's not.
Stu|||Hi Tony!

> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
Interesting... I have a feeling that many people does it the other way aroun
d (identity is PK, and
natural key(s) are UQ). Is it just by habit, or do you have any particular r
eason doing it that way?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> From the database design point of view the PRIMARY KEY should reflect the
natural key of your
> data.
> From the technical point of view, a PRIMARY KEY does not allow NULL's, whe
re as a UNIQUE
> Constraint / Index allows at least 1 NULL.
> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
> CREATE TABLE currency (
> id int not null identity constraint sk_currency unique clustered,
> code char(3) not null constraint pk_currency primary key nonclustered
> )
> CREATE TABLE trade (
> ...
> ...
> settlement_currency_id int not null references currency( id ),
> trade_currency_id int not null references currency( id ),
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Markus Zingg" <m.zingg@.nct.ch> wrote in message
> news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
>|||Hi Tibor - long time no beer....
My feeling and approach is this, most tables will have a natural key that we
need to provide uniqueness for, I make the natural key the PRIMARY KEY (it
also flows naturally from the logical design), the surrogate key is there as
part of the implementation phase to improve schema performance and
scalability hence I make it a unique constraint to enforce uniqueness, and
yes - use IDENTITY to auto populate.
There are a number of occaisions where there is no practical usable natural
key and in that instance I will just make the column with the IDENTITY
property the PRIMARY KEY and not bother with the surrogate.
I never use the natural key anywhere else - there is just one copy of it and
thats in its base table, and foreign key references I use the surrogate key.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCpTmJSaGHA.4544@.TK2MSFTNGP02.phx.gbl...
> Hi Tony!
>
> Interesting... I have a feeling that many people does it the other way
> around (identity is PK, and natural key(s) are UQ). Is it just by habit,
> or do you have any particular reason doing it that way?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
>