Re: help on composite keys!

Arne Jonsson wrote:
>
> We have a SQL-server database. It contains (among others) the tables
> students, certificates and a joining table between these two, called
> student/cert. It looks like this: studentID, certID, date, passed.
> Since a student can fail and make many attempts (so we can have no
> unique identifier) it´s advocated in our group that we shall have no
> pkey in the joining table, just student ID and certID as fkeys . Since
> all the gurus around in this group says there shall always be a pkey
> in a table I try to make my voice heard and tell we must have one and
> if it does´nt work it´s something wrong with the design. But that´s as
> far as my arguments goes. So I call for help to sort this out for me
> (and the others guys int the group). What drawbacks will there be if
> we have this joining table without any pkey? Maybe this is a case for
> Mr Hidders?

Apart from the consequences of not having a pkey (which I will leave for others to enumerate), you MIGHT already have a candidate key in the joining table: studentID, certID, date. As long as a student can't try for the same certificate twice on one day, this should be unique.

If the exams are taken by many students on each occasion (written exams or oral exams with more than one student), it might also be interesting to record the exact date/time of the exam. Then you have a third table exams and the joining table becomes: studentID, certID, examID, passed (or grade or result). This way you again have a natural pkey, since a student can't participate in an exam twice. The exam table could be: examID, date, (time, )(examinator, )(...)

hth,
Heinz Received on Tue Feb 20 2001 - 07:35:35 CET