Create foreign keys on Table Creation Only?

[geosite]

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?

[r937]

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

ALTER TABLE tbl_name 
  ADD [CONSTRAINT symbol] 
      FOREIGN KEY [index_name] (index_col_name,...)  
         [reference_definition]

[geosite]

of course not

allow me to introduce you to the ALTER TABLE statement

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:

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?

[r937]

ALTER TABLE tbl_Nations 
  ADD FOREIGN KEY ( CCode )  
      REFERENCES tbl_Continents ( CCode )

[geosite]

Whew, thanks. I don't know why it took me so long to find that BIG BOX!

[geosite]

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

[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

[geosite]

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.

[r937]

here's a tip

create a script

the script will look something like this:

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

[geosite]

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.