SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Primary Key on multiple columns

    Hei.
    At the university I have a part time job helping younger students in a course about MySQL (and MatLab).
    In the solution proposal to one of the exercises this syntax came up to create a new table in a database.
    Code:
    create table impression(
        matchnumber int not null,
        nickname varchar(20) not null,
        value int not null,
        primary key (matchnumber,nickname)
        );
    I found this astonishing because I thought that only one column in a table could have a PRIMARY KEY.
    So I looked in the manual and found this:
    • A PRIMARY KEY is a unique KEY with the extra constraint that all key columns must be defined as NOT NULL. In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
    • A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax.
    Is the code suggested above creating a multiple-column index?
    The code above is that a correct specification for a table in a database?

    The code doesn't return any errormessages.

    Any help and further explanation would be great!

    -Helge

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "Is the code suggested above creating a multiple-column index?"

    yes, but the creation of an index for a primary key is incidental

    an index is just the database's perferred mechanism for guaranteeing that a primary key value is unique

    "The code above is that a correct specification for a table in a database?"

    yup

    think of it this way: a primary key is not some feature that a column has, rather, it is a feature that a table has

    a table can have a primary key, or not -- but only one of them, even though it can consist of multiple columns

    the reason database systems (i know of no exceptions) use an index in order to implement a primary key is that when you go to insert a new value, the database has to ensure that the new value doesn't already exist, and the easiest and most efficient way for the database to do that is to look up the new value in an index

    if the database tried to look it up any other way (e.g. table scan) it would take forever, and people would end up complaining what a crappy database it is, and it would die

    rudy

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 for making this more clear to me!

    -Helge


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
  •