468,119 Members | 1,878 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

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

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.
Nov 12 '05 #1
5 6590
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...
Nov 12 '05 #2
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
Nov 12 '05 #3
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?
Nov 12 '05 #4
"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
Nov 12 '05 #5
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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Girish Agarwal | last post: by
18 posts views Thread by Thomas A. Anderson | last post: by
4 posts views Thread by Ismail Rajput | last post: by
7 posts views Thread by Ronald S. Cook | last post: by
15 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.