Compound and foreign keys

[OK, first of all, I know this is not a MySQL specific question, but I
couldn't find any newsgroup about SQL in general.]

Today I had an exam test for a course about data storage and processing,
with 20 true/false statements.

One of the statements was (translated from swedish):
"A compound key is a primary key based on at least two foreign keys."

I answered "False", but in the the published key answers they say "True".



What do you say about this? Do I really need two foreign keys to create a
compound key?

I have searched on google for the SQL standard definition of a compound key,
but I can't find any usefull information.



I have already emailed the teacher (no reply yet), but I though maybe you
can give me some arguments I could tell him _if_ he would claim that the
statement is correct.



Regards

/Jimi

Jimi Hullegård wrote:
> [OK, first of all, I know this is not a MySQL specific question, but I
> couldn't find any newsgroup about SQL in general.]

comp.databases is probably the closest thing to a general SQL newsgroup.

> One of the statements was (translated from swedish):
> "A compound key is a primary key based on at least two foreign keys."

Consider this example of a many-to-many table:

create table employeeProjectMapping (
employee_id integer not null references employee(employee_id),
project_id integer not null references project(project_id),
primary key(employee_id, project_id)
);

This fits the statement given in your exam. However, though the
statement is true, it is not a complete definition of a compound key.
It would have been more clear if the statement were, "One example of a
compound key is a primary key based on at least two foreign keys."

This is not the only use of compound keys. It is not unusual to define
a primary key over multiple columns, which are _not_ themselves foreign
keys.

Besides, the statement only mentioned "key" (unless there's an issue
with the translation from Swedish). I'm pretty sure "key" can refer to
a primary key, a foreign key, or even another set of columns that can be
used to identify records in the table.

In my opinion, you are correct in disputing this item on the exam. Good
luck!

Regards,
Bill K.

On Fri, 21 Oct 2005 20:39:44 +0200, Jimi Hullegård
<ask_for_real_email@nothotmail.com> wrote:
[snip]
>
> Today I had an exam test for a course about data storage and processing,
> with 20 true/false statements.
>
> One of the statements was (translated from swedish):
> "A compound key is a primary key based on at least two foreign keys."
>
> I answered "False", but in the the published key answers they say "True".
>
>
>
> What do you say about this? Do I really need two foreign keys to create a
> compound key?
>

It is possible to create a primary key using two columns. For example,
you might have a table with Month, Year, and Hits as columns. Month and
Year are not unique but can be when taken together (you won't have a
repeat of Month 10 and year 2005 for example). I have usually seen such
a key identified as a Composite Primary Key.
--
Chris Nestrud
Email: ccn@panix.com
http://www.panix.com/~ccn/

"Jimi Hullegård" <ask_for_real_email@nothotmail.com> wrote in message
news:YBa6f.994$zn6.676@nntpserver.swip.net...
> [OK, first of all, I know this is not a MySQL specific question, but I
> couldn't find any newsgroup about SQL in general.]
>
> Today I had an exam test for a course about data storage and processing,
> with 20 true/false statements.
>
> One of the statements was (translated from swedish):
> "A compound key is a primary key based on at least two foreign keys."
>
> I answered "False", but in the the published key answers they say "True".
>
>
>
> What do you say about this? Do I really need two foreign keys to create a
> compound key?

Your teacher is wrong. A compound primary key is made up of more than one
column, but those columns do NOT have to be foreign keys. They may be, but
they do not have to be. I have been designing and building databases for
over 20 years, so it is safe to assume that I know what I'm talking about.

--
Tony Marston
http://www.tonymarston.net

> I have searched on google for the SQL standard definition of a compound
> key, but I can't find any usefull information.
>
>
>
> I have already emailed the teacher (no reply yet), but I though maybe you
> can give me some arguments I could tell him _if_ he would claim that the
> statement is correct.
>
>
>
> Regards
>
> /Jimi
>
>