SitePoint Sponsor

User Tag List

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

    Create foreign keys on Table Creation Only?

    I've searched far and wide for a resource that clearly explains how to create a foreign key without success. I can't find the term "foreign key" in phpMyAdmin. I downloaded two other programs, MySQL Front and SQLyog, and I haven't yet found a "Create Foreign Key" command on either one.

    The MySQL Manual says:

    "You can create a foreign key by defining a foreign key constraint when you create a table."

    Does that mean you can only create a foreign key when you're creating a table? if you create a table without specifying a foreign key, then fill it with data, you're out of luck?

    To test it, I created a new table in phpMyAdmin, searching for the "Create Foreign Key" command. No luck.

    What's the magic secret?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by geosite
    Does that mean you can only create a foreign key when you're creating a table?
    of course not

    allow me to introduce you to the ALTER TABLE statement
    Code:
    ALTER TABLE tbl_name 
      ADD [CONSTRAINT symbol] 
          FOREIGN KEY [index_name] (index_col_name,...)  
             [reference_definition]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    of course not

    allow me to introduce you to the ALTER TABLE statement
    Code:
    ALTER TABLE tbl_name 
      ADD [CONSTRAINT symbol] 
          FOREIGN KEY [index_name] (index_col_name,...)  
             [reference_definition]
    Sheez, I think the light bulb in my head just lit up. I was going to ask, "So what do you do what it - where do you type or paste it?"

    But I checked phpMyAdmin one more time, and I think I found it - right under my nose. When I look at my table in Structure view, I see a big box under the heading...

    "Run SQL query/queries on database world"

    Is that where I put the code you gave me?

    If so, how would I adapt the code for a table named Nations, if I want the foreign key to be on a column (field) named CCode? I assume it would look something like this:

    Code:
    ALTER TABLE tbl_Nations 
      ADD [CONSTRAINT symbol] 
          FOREIGN KEY [index_name] (index_col_CCode,...)  
             [reference_definition]
    But I assume one of these elements is supposed to link the foreign key to the PRIMARY KEY in the table I'm going to join it with, right? If that table is named Continents, and the primary key is named ID, what would my code look like?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ALTER TABLE tbl_Nations 
      ADD FOREIGN KEY ( CCode )  
          REFERENCES tbl_Continents ( CCode )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whew, thanks. I don't know why it took me so long to find that BIG BOX!

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops - that doesn't seem to be working. I tried it and got a message that that table doesn't exist. Then I modified the code and got a different kind of error message -

    #1005 - Can't create table '.\world\#sql-6e4_57.frm' (errno: 150)

    And I caught it all on film!

    http://www.geoworld.org/fk.gif

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    keep trying, you're almost there

    you probably just used the wrong column name, like the wrong table name

    by the way, unless these are innodb tables, you are really not going to get anything from this exercise
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    keep trying, you're almost there

    you probably just used the wrong column name, like the wrong table name

    by the way, unless these are innodb tables, you are really not going to get anything from this exercise
    Oops - one wasn't InnoDB. However, I changed it, and it still doesn't work.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here's a tip

    create a script

    the script will look something like this:
    Code:
    create table somedates2
    ( id tinyint not null primary key auto_increment
    , date1  datetime
    , date2  datetime
    );
    insert into somedates2 ( date1,date2 ) values 
      ( '2004-05-11 12:00:00', '2004-05-12 12:00:00' )
    , ( '2004-05-11 12:00:00', '2004-06-11 12:00:00' )
    , ( '2004-05-11 12:00:00', '2005-05-11 12:00:00' )
    , ( '2004-05-11 12:00:00', '2004-05-11 12:01:00' )
    , ( '2004-05-11 12:00:00', '2004-05-11 13:00:00' )
    , ( '2004-05-11 12:00:00', '2004-05-12 11:59:59' )
    ;
    select id
         , unix_timestamp(date2)
          -unix_timestamp(date1)   as unixdiff
         , floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                )                              as daysdiff
         , unix_timestamp(date2)
          -unix_timestamp(date1) 
         - floor(
           ( unix_timestamp(date2)
            -unix_timestamp(date1) ) / 86400
                ) * 86400                         as secondsdiff
      from somedates2
    what is the purpose of the script?

    so that you can drop the table, and start all over again

    make a change, re-run, didn't work?

    drop the table, and start all over again

    that way, you will not have to constantly futz with ALTER

    now, this means you may have to (re)create several tables in the script, but i think it's a better testing methodology than trying to fix stuff that was fixed after a fix that didn't work...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    here's a tip
    create a script

    the script will look something like this:

    Holy cow, that looks even more complex; I don't understand it at all. I'll add it to my notes, but I have more trick up my sleeve to try first.

    Thanks.


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
  •