SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
-
May 12, 2004, 11:14 #1
- 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?
-
May 12, 2004, 13:09 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by geosite
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]
-
May 12, 2004, 13:26 #3
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
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]
-
May 12, 2004, 13:57 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:ALTER TABLE tbl_Nations ADD FOREIGN KEY ( CCode ) REFERENCES tbl_Continents ( CCode )
-
May 12, 2004, 14:15 #5
- 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!
-
May 12, 2004, 14:35 #6
- 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
-
May 12, 2004, 14:48 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
May 12, 2004, 15:57 #8
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
-
May 12, 2004, 16:06 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
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...
-
May 12, 2004, 16:24 #10
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
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