Q: Are composite primary keys in joint tables good practice?

[John]

Specifically for joint tables...

tblStudents
tblClasses
tblClasses_Students

Is it be good programming to use a composite primary key in
tblClasses_Students (where the key is ClassID and StudentID)? I
enventually will convert the Access db to an SQL db. I've always used
single primary keys based on long integers, usually autonumbered.

[Pieter Linden]

so*********@hotmail.com (John) wrote in message news:<90**************************@posting.google. com>...

Specifically for joint tables...

tblStudents
tblClasses
tblClasses_Students

Is it be good programming to use a composite primary key in
tblClasses_Students (where the key is ClassID and StudentID)? I
enventually will convert the Access db to an SQL db. I've always used
single primary keys based on long integers, usually autonumbered.

The composite PK will automatically disallow duplicates, although you
can add an autonumber field if that child table will have children of
its own...

[Bob Quintal]

so*********@hotmail.com (John) wrote in
news:90**************************@posting.google.c om:

Specifically for joint tables...

tblStudents
tblClasses
tblClasses_Students

Is it be good programming to use a composite primary key in
tblClasses_Students (where the key is ClassID and StudentID)?
I enventually will convert the Access db to an SQL db. I've
always used single primary keys based on long integers,
usually autonumbered.

It is actually better programming practice than using an
autonumber, as autonumbers are application dependent.

Bob Q

[John]

I was told this in regards to composite PKs:

One of the problems with a primary compound key is that the error
messages are no longer under your control. If a duplication occurs for
a set of foreign keys, the cryptic system-generated error message will
confuse the user. If you check for the error in code, you can write a
much more user-friendly message that tells the user what the problem
is and how to correct it. This reduces the amount of IT support
required. If this intersection table is generated within the
application, not by manual entry, then your code should prevent such
duplications from occurring.

Any comments about this suggestion?

[Rick Brandt]

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I was told this in regards to composite PKs:

One of the problems with a primary compound key is that the error
messages are no longer under your control. If a duplication occurs for
a set of foreign keys, the cryptic system-generated error message will
confuse the user. If you check for the error in code, you can write a
much more user-friendly message that tells the user what the problem
is and how to correct it. This reduces the amount of IT support
required. If this intersection table is generated within the
application, not by manual entry, then your code should prevent such
duplications from occurring.

Any comments about this suggestion?

Apples and oranges. One can apply a unique index on a composite set of
fields that is NOT the PK and therefore still get a cryptic message when
using a surrogate PK. One can also check for duplicates in code against a
composite PK and display a user-friendly message *before* the more cryptic
one has a chance to fire or trap for the cryptic message from the system
and replace it with a more friendly one.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[TC]

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I was told this in regards to composite PKs:

One of the problems with a primary compound key is that the error
messages are no longer under your control. If a duplication occurs for
a set of foreign keys, the cryptic system-generated error message will
confuse the user. If you check for the error in code, you can write a
much more user-friendly message that tells the user what the problem
is and how to correct it. This reduces the amount of IT support
required. If this intersection table is generated within the
application, not by manual entry, then your code should prevent such
duplications from occurring.

Any comments about this suggestion?

Yep: the basic premise that with a composite key "the error messages are no
longer under your control", is just not so!

You can check for duplicates before they occur, or handle them after they
occur, using various means, none of which (AFAIK) are significantly
different for composite keys than they are for non-composite keys.

HTH,
TC