Re: help on composite keys!

On Wed, 21 Feb 2001 09:47:39 +0100, Heinz Huber <Heinz.Huber_at_elbanet.co.at> wrote:

>
>
>Arne Jonsson wrote:
>>
>> 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.
>>
>> 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?
>
>I hope you don't mind if I step in on this ;-)
>
>The clue is that you don't have a uniqueness constraint on any of the
>three columns alone. You define a primary key that consists of all three
>columns:
>CREATE TABLE StudentExams (
> studentID INTEGER NOT NULL,
> certID INTEGER NOT NULL,
> date DATE NOT NULL,
> passed BOOLEAN (?),
> PRIMARY KEY (studentID, certID, date));
>
>A candidate key is a combination of columns that are unique for a given
>relation. In this case studentID, certID, date are probably a candidate
>key. And they are probably the only combination of columns that are
>unique for StudentExams. That's why Jan said that he can't see another
>candidate key.
>
>If you have a relation with more than one candidate key, you have to
>pick one to be the primary key.
>
>hth,
>Heinz

Thank´s a lot to all of you guys! I was definitively on the wrong path in my last question, or maybe even totaly off the path. I´m just home from my course(a VB-course actually, that´s why we all are a bit lame on this subject) and this morning I tried exacty the code Heinz wrote above, and it worked just fine. I just thought it would be more complicated!
To Jan I would like to say that the other guys in the group bought the explanation why you need a pkey, so now there is me plus nine more who are enlightend. That´s a good shot!
Kind regards
Arne Received on Wed Feb 21 2001 - 17:25:07 CET