Relational theory and practise say that every row MUST be unique in a table.
You only have the contents of the row to distinguish between one row and the
next.  So say I store my customers like this

Initial	Surname	Address		Balance
S		Carter	53 Scott Street	-500.00

Well that's fine but what if there are two S Carters at that address (my mum
happens to be called Sue, though I don't live in her house any more!)

S		Carter	53 Scott Street	2130.00

So then I go 

UPDATE customers SET balance = balance + 40.00 WHERE Intital = 'S' AND
Surname = 'Carter' AND Address = '53 Scott Street'

How does that work?  Well we get 40 dollars each.

You can't say 

UPDATE customers 
SET balance = balance + 40.00 
WHERE Intital = 'S' 
	AND Surname = 'Carter' 
	AND Address = '53 Scott Street'
NO NOT THAT ONE THE OTHER ONE

So you need a simple way of guaranteeing that your rows are unique.  By
adding an otherwise meaningless number and forcing it to be unique for every
row, you gain the ability to refer to an individual row with very little
overhead.  It's quicker to index than a string and smaller to pass around
than most data types.

When implementing a table of users it's tempting to use the user ID as the
unique identifier, since it is both unique and an identifier.  I can't
really comment on whether it's worthwhile doing this instead of using an
autonumber, except to say that once you are in the habit of putting an
autonumber on every table and defining it as your primary key there is no
reason not to do it in this case either (unless space is at a premium, and
even then there are bound to be much greater savings you can make
elsewhere.)

HTH,

Steve.

PS.  Your terminology is a bit skewiff: you ALWAYS need a primary key - it
just isn't necessarily in a column of its own.  For instance, I could have
said that the primary key of my Customers table above was a composite of
Initial, Surname and Address.  Of course that would mean that you simply
couldn't have two people living in the same house with the same initial and
surname.  In fact my primary key was the whole row and the design is duff
anyway.

Cheers


> -----Original Message-----
> From: speedguru@m... [mailto:speedguru@m...]
> Sent: 07 September 2001 07:58
> To: sql language
> Subject: [sql_language] Primary key
> 
> 
> hi,
> 
>  some of my peers say its good db design to have a Primary key set in 
> every table. Even Access suggests you that you set it  before 
> a schema is 
> saved
> 
>   what are the advantages ? is it good to add , maybe a 
> autonumber field 
> eventhough it does not really relate to my table.
> 
>  can anybody suggest good resources ( books or websites )on 
> DB design .
> 
> ty,
> :-)rk
> 
>