Re: help on composite keys!

On 20 Feb 2001 10:51:15 GMT, hidders_at_REMOVE.THIS.win.tue.nl (Jan Hidders) wrote:

>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?
>
>It certainly is. Just let me get my hat, trenchcoat and gun, and the I
>will get right on it. For the ususal fee, plus expense, of course. :-)
>
>Actually you are asking two questions. The first is 'Why do we need a
>primary key?' and the second is 'What would be the primary key in this
>case?'.
>
>The first question is usually answered with two reasons. The first
>reason is that you do not want to have the same record twice in the
>table. This is because we interpret records as facts. A certain record
>in the student/cert table represents the fact that a certain student
>has done a certain exam on a certain date with a certain result. It is
>not clear what the meaning would be if there are two records in the
>table with exactly the same fields. It is either true, or it isn't, but
>a fact cannot be "double true". The second reason for a primary key is
>a more practical one. If you want to have a foreign key to this
>relation then you will choose the primary key as the foreign key in the
>referring table. So if you want to have a relationship between two
>tables you will always choose the same foreign key. That keeps things
>simple and it makes life easier for the DBMS.
>
>In your case the first argument is probably the most important one.
>Note that it may seem a little philosphical but it is deeply rooted in
>practical experience. For instance, the primary key makes sure that the
>database detects it if a certain fact that a user is trying to add to
>the table is already present in the table.
>
>The second question was what the primary key would be in this case. In
>principle you always have to look first for the candidate keys and then
>decide which one you are going to take as the primary key. So what are
>the attributes that uniquely identify a record? This depends of course
>on the specific case but I think it is reasonable to assume that a
>student can only apply once for a certain certificate on a specific
>date. So the combination {studentID, certID, date} would be a candidate
>key. Since I do not see any other candidate keys at this moment, this
>would also be the choice for the primary key.
>
>Kind regards,
>
>--
> Jan Hidders

Thanks, I think I begin to see the light, at least when it comes to the udnerstanding of the need of a primary key. But how do you actually write the code if the three fields all must have duplicate values? For example student 1 makes an attempt for certifikate 1 on day 1 and failes. The same day student 2 tries for certificate 1. Student 1 makes a new try for certificate 1 another day and so on. As I see it none of the fields can have a unique constraint. Am I on the wrong path here or is it just some trick in the CREATE TABLE syntax? And one more before You take your hat off: the term candidate key I´ve seen quite a few times before but never really understood. You think you can expalin it for a newbie?

Kind regards

Arne Jonsson Received on Tue Feb 20 2001 - 22:45:35 CET