Professional Web Applications Themes

Compound and foreign keys - MySQL

[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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Compound and foreign keys

    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.
    Bill Karwin Guest

  3. #3

    Default Re: Compound and foreign keys

    On Fri, 21 Oct 2005 20:39:44 +0200, Jimi Hullegård
    <ask_for_real_emailnothotmail.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: [email]ccnpanix.com[/email]
    [url]http://www.panix.com/~ccn/[/url]
    Chris Nestrud Guest

  4. #4

    Default Re: Compound and foreign keys


    "Jimi Hullegård" <ask_for_real_emailnothotmail.com> wrote in message
    news:YBa6f.994$zn6.676nntpserver.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
    [url]http://www.tonymarston.net[/url]
    > 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
    >
    >

    Tony Marston Guest

Similar Threads

  1. Foreign keys tut
    By frizzle in forum MySQL
    Replies: 2
    Last Post: July 18th, 05:45 PM
  2. foreign keys
    By Sybrand Bakker in forum Microsoft Access
    Replies: 2
    Last Post: July 25th, 10:04 PM
  3. foreign keys - updated
    By Josh in forum Microsoft Access
    Replies: 2
    Last Post: July 24th, 04:01 PM
  4. DMO + Foreign Keys
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 05:58 AM
  5. SQL DMO + Foreign Keys
    By Steven Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 09:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139