Re: help on composite keys!

From: Heinz Huber <Heinz.Huber_at_elbanet.co.at>
Date: Wed, 21 Feb 2001 09:47:39 +0100
Message-ID: <3A9380AB.437A756C_at_elbanet.co.at>

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 Received on Wed Feb 21 2001 - 09:47:39 CET