SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Working With Keys

    I have a table that beings with a numerical field (1-273) named "ID," which I designated my primary key, which I understand is standard practice. But I want to use abbreviations for my primary key instead, so I'm trying to switch the primary key to a different field, "CCode," but I can't figure out how to do it with either phpMyAdmin or MySQL Front.

    I figured out how to drop the primary key, but I can't apply it to the other field - CCode. I made two screenshots to illustrate the steps I followed and the error messages I received with both phpMyAdmin and MySQL Front:

    http://www.geoworld.org/primarykey.gif
    http://www.geoworld.org/primarykey2.gif

    Any tips?

    Also, how do you create a foreign key? I haven't yet found the instructions on either phpMyAdmin or MySQL Front.

    P.S. When phpMyAdmin uses the word "Index," is that another word for "Key"?

  2. #2
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did a quick test and didn't have any problems assigning a primary key to a different field.

    The other thing is that you could use alias to temporary rename your column to something else.

    Code:
    SELECT
        ID as CCode
    FROM
        nations
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  3. #3
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think mySQL actually allows foreign key definitions. You have to enforce integrity yourself. Not hard really.

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Toly
    I did a quick test and didn't have any problems assigning a primary key to a different field.[/PHP]

    A quick test? Did you recreate my table, or a portion of it? If so, thanks for the effort!

    "The other thing is that you could use alias to temporary rename your column to something else."

    Code:
    SELECT
        ID as CCode
    FROM
        nations
    Is that something you can do with phpMyAdmin or MySQL Front, or do you have to do it from a command line?

    At any rate, I tried something similar. I copied another table that already had a similar column designated the primary key, then tweaked it a bit. I still got an error message when I tried to import data, but I just clicked through the messages until all the data was imported.

    Then, I suddenly realized what the real problem probably is.

    My table features five columns filled with data for each of the world's nations, incuding the abbreviations (e.g. us for United States) that I'm using for my primary key.

    However, I also inserted some hearders - like North America at the top and South America at the top of the South American nations. On those rows, the corresponding abbreviation cells are empty.

    So when I designated my primary key NOT NULL - when some cells were in fact empty - that probably threw things out of wack, right?

    Now that I've imported the data, will that be a continuing problem? Do I have to stick something in those emty cells - like - or an em dash - or is it OK to leave a few empty cells on a primary key?

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Subjective Effec
    I don't think mySQL actually allows foreign key definitions. You have to enforce integrity yourself. Not hard really.
    So how do you do that?

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geosite
    So how do you do that?
    Nevermind... I found MySQL's foreign key page...

  7. #7
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,182
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geosite
    Is that something you can do with phpMyAdmin or MySQL Front, or do you have to do it from a command line?
    Yes, you do it from the command line or when you are calling a query within a script.

    Now that I've imported the data, will that be a continuing problem? Do I have to stick something in those emty cells - like - or an em dash - or is it OK to leave a few empty cells on a primary key?
    I think there might be some issues with empty rows on a not null primary key field, but I can't be sure since I'm no sql expert. I'm sure someone else will be able to answer that question.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  8. #8
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Holy cow, I took another look at the MySQL Manual, and I'm more confused than ever!

    From square one, please refer to the first two tables in the screenshot at
    http://www.geoworld.org/worlddatabase.gif

    The first table features rows for every continent and ocean, ending in a field named "CCode," for Continent Code. It's simply a list of abbreviations that I want to designate my primary key.

    The second table features a row for every nation, ending with the CCode field, which will be designated the foreign key (as soon as I figure out how to make foreign keys!).

    There are two things that confuse me. First, there are several nations in most of the continents. Thus, the foreign key has a value of "na" (North America) for Canada, U.S. and Mexico. Several dozen nations will have a foreign key value of "eur" (Eurasia) and "afr" (Africa).

    Is this OK? Something I read led me to suspect that no element can be repeated in a foreign key, but that doesn't make sense. I can understand why every element in a PRIMARY key should be unique, but not a foreign key.

    Second problem: I included rows with subheadings in this table for each of the continents and oceans.

    If you look at the very top row in the Nations table, you'll see it features data in just one cell - the words "NORTH AMERICA." That row's foreign key cell is marked NULL.

    So here's my question: Does every cell in a foreign key have to have a value? If so, can I just insert something like a dash (-) if I don't want to link to that row to a primary key on another table?

    Or should I go ahead and link the North America row to North America on the Continents table?

    If I can resolve this, then I can repeat the process, establishing NCode as the primary key on my states table, and using its values to make a foreign key on the Counties table.

    Also, I don't understand how to create foreign keys. From the MySQL Manual:

    http://dev.mysql.com/doc/mysql/en/In...nstraints.html

    "Both tables must be InnoDB type. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order."

    First, suppose we just have two tables. The Continents table has just one key - a primary key. The Nations table has just one key - a foreign key that matches the Continent table's primary key.

    Which table is the "referencing table"?

    And what does this mean?: "There must be an index where the foreign key columns are listed as the first columns in the same order."

    Are they saying the foreign key has to be the first field? And why do they speak of foreign key columnS? I thought there could only be one foreign key per table.

    "In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order."

    Again, this is confusing. Which is the referenced table? What kind of index are they talking about - a primary key? Foreign key? It has to be the first column/field? And what do they mean by "the same order"? Are they saying that if NA is listed first in the Continent table's primary key, it also has to be first in the Nation table's corresponding foreign key?

    "InnoDB does not automatically create indexes on foreign keys or referenced keys: You must create them explicitly."

    So how do you create indexes?

    There's more. This is very confusing.

    Are you aware of any tutorials that feature pictures illustrating this concept and clearly spell out what steps you have to take?

    Thanks.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    First, there are several nations in most of the continents. Thus, the foreign key has a value of "na" (North America) for Canada, U.S. and Mexico. Several dozen nations will have a foreign key value of "eur" (Eurasia) and "afr" (Africa).

    Is this OK? Something I read led me to suspect that no element can be repeated in a foreign key, but that doesn't make sense. I can understand why every element in a PRIMARY key should be unique, but not a foreign key.
    it's OK, and you are absolutely right, foreign keys can repeat

    otherwise nobody would be able to implement a 1-to-many relationship

    [quote]So here's my question: Does every cell in a foreign key have to have a value? [quote]absolutely not

    see Optional foreign key can be NULL (site registration may be required, but it's free)

    First, suppose we just have two tables. The Continents table has just one key - a primary key. The Nations table has just one key - a foreign key that matches the Continent table's primary key.

    Which table is the "referencing table"?
    that would be the Nations table

    the foreign key is always on the "many" side of the 1-to-many relationship

    the foreign key "references" the primary key

    seeing the syntax should make it clear --
    Code:
    create table Nations
    ( ...
    , constraint validcontinent foreign key (CCode) references Continents (CCode)
    );
    And what does this mean?: "There must be an index where the foreign key columns are listed as the first columns in the same order."
    it means that if the primary key is A1,B1,C1, then the foreign key will be A2,B2,C2, and you need to declare an index on A2,B2,C2 in that order, with nothing else at the beginning (you can add other columns after the FK columns)

    Are you aware of any tutorials that feature pictures illustrating this concept and clearly spell out what steps you have to take?
    yes, Data Modeling by the university of texas at austin
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •