Primary Key: I am trying to understand--please help if you can.

Hi all,

So I've been reading the newsgroups, and reading a few books trying to learn
SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
every table should have one. I know (I think) that a Primary Key is a
special field that uniquely identifies each record or row within a table.

My question is this: If I have a field or column whose values are all (and
will be) different in every row, is that what a Primary Key is, or is it not
a primary key until I designate it as such with either a statement or by
right-clicking a little key on the table diagram.

If so, then what is the reason _behind_ why I have to do that, and have a
primary key?

I've been playing with a few tables, and I can select information just fine,
relate tables together with joins, and everything works fine (so far) all
without my designating any Primary Keys--so why, exactly, do I need one, and
exactly what is it?

I would appreciate any help and clarification.

Thanks.Replied in microsoft.public.sqlserver.programming.

Please don't multi-post.

--
David Portas
SQL Server MVP
--|||David Portas scratched out in the sand

> Replied in microsoft.public.sqlserver.programming.
> Please don't multi-post.

...and for those of us who don't subscribe to m.p.s.p, your answer, David,
was...?

--
kai - kai at 3gproductions dot com
www.gamephreakz.com || www.filesite.org
"friends don't let friends use windows xp"|||Hi Mavis,

As you suggest, a Primary key is a constraint on the column requiring that
every value is (and will be) different
in every row. Furthermore, it requires that no row can have a NULL value for
that column. So those are the restrictions
on a Primary Key field, that it be non-null and that each value be unique.
Further, a Primary Key can be made up of more than
one column. So, each table can have only one Primary Key, but it can be
made up of several columns. (jeesh- so far I'm probably
just making this MORE confusing).

So, although those are the characteristics of a Primary Key, a column is not
one until you designate it as such. The reason behind it is
twofold (there may be more reasons, but only two come to mind at the
moment - Note to self: more coffee):
1. Performance - when you designate a column as a Primary Key, it is
(clustered) indexed to assist in data retrieval.
2. Data Integrity - the database will ensure that every value that is
inserted (or updated) in the table will meet the requirements of a
Primary Key (i.e. unique and non-null) so you don't have to write those
checks into application code.

It's assumed that the Primary Key field will typically be the field used in
table joins, so because it's indexed (it's also a good general plan
to index the foreign key field) your queries will perform better.

I hope this is of some help Mavis,

Steve

"Mavis Tilden" <mavistilden@.hotmail.com> wrote in message
news:XVead.9397$3C6.206028@.news20.bellglobal.com.. .
> Hi all,
> So I've been reading the newsgroups, and reading a few books trying to
learn
> SQL and SQL Server 2000. The books tell me I need a Primary Key, and that
> every table should have one. I know (I think) that a Primary Key is a
> special field that uniquely identifies each record or row within a table.
> My question is this: If I have a field or column whose values are all (and
> will be) different in every row, is that what a Primary Key is, or is it
not
> a primary key until I designate it as such with either a statement or by
> right-clicking a little key on the table diagram.
> If so, then what is the reason _behind_ why I have to do that, and have a
> primary key?
> I've been playing with a few tables, and I can select information just
fine,
> relate tables together with joins, and everything works fine (so far) all
> without my designating any Primary Keys--so why, exactly, do I need one,
and
> exactly what is it?
> I would appreciate any help and clarification.
> Thanks.|||On Mon, 11 Oct 2004 04:50:11 -0700, filesiteguy wrote:

>David Portas scratched out in the sand
>> Replied in microsoft.public.sqlserver.programming.
>>
>> Please don't multi-post.
>>
>...and for those of us who don't subscribe to m.p.s.p, your answer, David,
>was...?

Hi filesiteguy,

http://www.google.com/groups?hl=en&...bellglobal.com

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)