Is primary key always recommended

[dmcc...@my-deja.com]

Is a primary key always recommended? Does it
increase performance to have one even if the key
field will not be used to query the table?

For example:
Imagine two tables -- occupant and telnum where
there is a one to many relationship between an
occupant and telephone numbers. The telnum table
contains 2 fields: a foreign key field to the
occupant table and the telephone number. Even
though the join will always be made on the
occupant_key field, does it increase performance
to create an artificial primary key like an
autonumber? So instead of just a "non-unique"
index on the occupant_key, you would have a
unique index on the primary_key.

Thanks

David

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[bb...@smpsn.com]

If you will NEVER have any relationship there and
NEVER query it, it probably won't care (performance
wise)

However, you'll find that most external tools
like Powerbuilder, Access etc.etc.. won't touch
an SQL table without a primary key.

We put 'em there even if we aren't specifically
going to use them.

In article <7prnm9$59d$1...@nnrp1.deja.com>,

[Danny Lesandrini]

I'm not an SQL Server guru (I've spent more time on the development side),
but everything I've ever read (and I've read a lot) advises that you select
a primary key.

You don't have to create an additional autonumber (IDENTITY) field, but
performance is increased if the datatype of the field you have selected as
primary is numeric ( INT, SMALLINTTINYINT or NUMERIC) instead of Text or
Date and you never want to use FLOAT.

In the case that you described, it doesn't seem to me that anything would be
gained by adding a primary key IDENTITY field. If telephone numbers are
unique (and numeric, not text) I would make that field primary.

On the other hand, you didn't mention how many records will likely be in the
table. I hate it when people tell me this, but here goes: Test it both
ways to see which is faster.

Have fun.
Work hard.
--

Danny Lesandrini
dlesa...@hotmail.com

<dmcc...@my-deja.com> wrote in message news:7prnm9$59d$1...@nnrp1.deja.com...

[bze...@my-deja.com]

The primary Key just validates the uniqueness of the record and nothing
else. Most of the text book refer to this a data integrity. in reality
you can get away without having one if you Business logic does not
require one. It is highly recommended that you use one sort of primary
Key, either an Identity Key or a self chosen one.
I think your performance will suffer depending on your Data, selection
of primary key or composite Key. IF you build a system with IDENTITY
Keys only some system build the PK with clustered Index (MS SQL)
Depending on your data the Server build the Index from scratch, which
will result in performance reduction.

I have seen some systems which are purely based on IDENTITY KEY as PK
and build additional Indexes as needed. I am not sure if a performance
increase is achieved but on some instances the designed stored procs
will
have to do more work with comparison logic, which will result in
performance degradation.

I think Danny is right, it depends on the application, server and most
importantly on the business logic.
For example, if you would like to change the telnum of an occupant and
your telnum is PK, that PK is used everywhere and you have to change it
in multiple places which brings referential integrity issues.

I am currently building a transaction system and a good mix of PKs as
IDENTITY and as composite key is a good choice.

Hope this will help
bz

In article <qVgw3.567$uE2....@news.uswest.net>,

[Charles R. Hoffman]

I am of the school that says if you are building a relational database then
every table must/should have a primary key. If you do not have primary
keys, you can not have foriegn keys and hence you cannot build any
relationships in your database so why are you using a relational database?

[Martin van Gils]

Hear, hear.
Charles R. Hoffman wrote in message
<7q1lkk$hbd$1...@nntp4.atl.mindspring.net>...

[Joe Maloney]

Academically speaking, in an RDBMS every table (tuple) should have a
primary key to avoid data duplication.

In the real world, not every table requires one, but that depends on
the business/application logic and performance needs.

If the table is small, a primary key can actually hurt retrieval
performance (requireing an index check/read then a data read when in in-
core search of a table that fits in one read page would be quicker).

If there is a parent-child relationship (1 to many for you youngsters),
then a primary key in the child table may be unnecessary, and again
hurt performance on loads (more indexes to update).

The occupant/phone cited in the start of this thread is a good example.
As long as it is maintained tightly so that the phone numbers always
point to the current user/occupant. If you want to know who had that
number last week, that is a different story.

The other issue here is that occupant/phone (depending on the
application) may actually be many-to-many. Example, I am an occupant
with a phone number, my wife is also an occupant with the same phone
number. We actually have two phones, so the relationship is many-to-
many. Almost by definition, a many-to-many relationship prevents
primary keys somewhere.

In article <7q2rti$jgq$2...@zonnetje.nl.uu.net>,

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.