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?

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