When NOT to use a primary key

> In a match table like the one being discussed here, the two foreign keys
> together should be declared as a composite primary key to avoid any
> duplicates.

Ah! This is a new concept to me. Off to look it up...

-Darrel

"Tom Muck" <tommuck@NOSPAM.basic-drumbeat.com> wrote in message
news:e205vq$o8v$1@forums.macromedia.com...

> True. I still stand by my original statement that it is sometimes a good
> idea to have a primary key. I recently did a menu system where it made
> sense to pass one value (a primary key from a many to many table) from a
> URL to a stored procedure rather than the two composite keys.
>

I can see that. You always want a primary key, but I think you're referring
to a surrogate key (autonumber style) above on a table that has a natural
key already. Sometimes it's nice to give yourself a one-column "handle" you
can use to grab onto a row even if there's already a natural key. That
being said, everything I do is tailored for very large databases where my
single biggest concern is the size of the data involved in any query. For
me, an additional column acting as surrogate key on this type of match table
means two things: first, that I end up creating an index over the two
columns that would have been indexed automatically had I made them the
primary key, and second, that my match table is a third again larger than it
needs to be.

That's why it's so hard to answer people when they ask what's best in
certain situations.

As a certain favorite author of mine likes to say, "there are no solutions;
there are only tradeoffs."

> No, not quite. EM can delete any record that can be identified uniquely
> whether or not that row has a key.
>
> In a match table like the one being discussed here, the two foreign keys
> together should be declared as a composite primary key to avoid any
> duplicates.
>

True. I still stand by my original statement that it is sometimes a good
idea to have a primary key. I recently did a menu system where it made sense
to pass one value (a primary key from a many to many table) from a URL to a
stored procedure rather than the two composite keys.

Tom

"Tom Muck" <tommuck@NOSPAM.basic-drumbeat.com> wrote in message
news:e1oi9c$mie$1@forums.macromedia.com...
> If you are deleting records in a database admin (not that I recommend
> this, but practically speaking it is something that is done occasionally)
> you will get an error if the record does not have a unique key field.
>

No, not quite. EM can delete any record that can be identified uniquely
whether or not that row has a key.

In a match table like the one being discussed here, the two foreign keys
together should be declared as a composite primary key to avoid any
duplicates.

> If you are deleting records in a database admin (not that I recommend
> this, but practically speaking it is something that is done occasionally)
> you will get an error if the record does not have a unique key field.

Interesting. I've never had that problem, but maybe it depends on which
application you are using.

> SELECT c.ClientName from JobClients
> WHERE JobsClients.ID = 50
>
> is slightly faster than:
>
> SELECT c.ClientName from JobClients
> WHERE JobsClients.JobID = 5
> AND JobsClients.ClientID = 2

Oh, sure, but in a join, you wouldn't know the JobsClients.ID to begin with,
right?

The link table isn't typically queried by itself. Or maybe I just haven't
come across that situation where i've needed to yet.

-Darrel

In this situation, I'd have 3 tables:

Empoyees:
EmpoyeeID (pk)
Employee

Skills:
SkillID (pk)
Skill

EmployeeSkills:
EmployeeSkillID (pk)
EmployeeID (fk)
SkillID (fk)

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

> Hmm...can you show an example? As for joins, you need to join on the FK's
> anyways from what I can tell, and as far as deleting, how is deleting a
> row via the primary key any different than deleting it via the FKs?

If you are deleting records in a database admin (not that I recommend this,
but practically speaking it is something that is done occasionally) you will
get an error if the record does not have a unique key field.

> (Not that I don't believe you...I'm just not following the logic...would
> love to understand that part better, as I'm certainly not a DB expert...)

SELECT c.ClientName from JobClients
WHERE JobsClients.ID = 50

is slightly faster than:

SELECT c.ClientName from JobClients
WHERE JobsClients.JobID = 5
AND JobsClients.ClientID = 2

Not much faster, but there are situations where it may be easier to use one
key field. In a web app, for example, it is easier to load a form field with
one key value than it is to load two form fields with two foreign key
values. If you are using a checkbox to add/delete records from a web form,
the checkbox can contain the PK field (the unneeded field) instead of the
two foreign keys.

Tom

> Faster joins, easier to update rows if needed, and easier to delete rows
> in
> a m-t-m table if it has an auto-generated key (did you ever try to delete
> records in the Enterprise Manager that did not have a primary key?)

Hmm...can you show an example? As for joins, you need to join on the FK's
anyways from what I can tell, and as far as deleting, how is deleting a row
via the primary key any different than deleting it via the FKs?

(Not that I don't believe you...I'm just not following the logic...would
love to understand that part better, as I'm certainly not a DB expert...)

-Darrel

.oO(Tom Muck)

>> * Ok, maybe someone can come up with a reason, but I can't think of one.
>> ;o)
>
>Faster joins, easier to update rows if needed, and easier to delete rows in
>a m-t-m table if it has an auto-generated key (did you ever try to delete
>records in the Enterprise Manager that did not have a primary key?)
>
>Theoretically it's not needed, but practically it is useful.

Of course the PK should be declared explicitly, but in such a "link"
table there's no need for an extra auto-generated key, the two existing
columns are enough.

Updating/deleting issues are better handled automatically with FK
constraints and ON UPDATE/ON DELETE clauses.

Micha

.oO(darrel)

>> In general, when can I disregard the primary key in a SQL linked table
>> design?
>
>link tables never* need a primary key.

JFTR: They need a PK, but ...

>They are already unique via the pair
>of FK keys in each record.

... these two columns together already _are_ the PK, so there's no need
for an extra field.

Micha