Chris.P Posted April 20, 2007 Share Posted April 20, 2007 mysql version: 4.1.21 I am creating a database using MySQL and need to create a foreign key. From what I have read this is not possible in MySQL? I have browsed through phpMyAdmin and cannot see the option so far. I have not tried doing it through basic MySQL commands as of yet as I don't know how to do it without rebuilding my tables. If it is not possible I presume the only way will be to throw all the fields into one table? Any help much appreciated. Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/ Share on other sites More sharing options...
Chris.P Posted April 20, 2007 Author Share Posted April 20, 2007 I can't for the life of me figure out how to edit my post on this forum so heres a reply of a little more info on what I'm trying to do. I have two tables one with user info inside it and one with details about images uploaded, obviously when I user logs in I want to call the user data from the user table and the related user picture from the picture table. Best way I presumed to do this would be to use a foreign key like so. User Table ImageTable ID ID Name Location DOB Size etc... Obviously ID primary in User table and foreign in Image Table. :-\ EDIT: Now I see how to edit although the edit button was covered up by that google ad in my first post, how annoying! :-\ Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-233859 Share on other sites More sharing options...
bubblegum.anarchy Posted April 20, 2007 Share Posted April 20, 2007 The InnoDB storage engine is required to implement foreign keys to providing the cascading effect... but the explicit definition of a foreign key is not required if you simply want to link two tables via a reference id, just be sure to index the referencing id and you can use the default low memory myISAM storage engine. Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-233871 Share on other sites More sharing options...
Chris.P Posted April 20, 2007 Author Share Posted April 20, 2007 just be sure to index the referencing id and you can use the default low memory myISAM storage engine. Ok, how do I go about doing that? ??? Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-233874 Share on other sites More sharing options...
bubblegum.anarchy Posted April 20, 2007 Share Posted April 20, 2007 using ALTER [iGNORE] TABLE tbl_name ADD INDEX [index_name] [index_type] (index_col_name,...) in something like: ALTER TABLE tbl_name ADD INDEX (id); Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-233887 Share on other sites More sharing options...
Chris.P Posted April 24, 2007 Author Share Posted April 24, 2007 Ok cheers I have added an index to one of the tables, should I have added it to any particular one? And also if I set an index on the id field of one table how does it know its indexing the index of the other table? A little confused I think. ??? Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-236937 Share on other sites More sharing options...
cmgmyr Posted April 24, 2007 Share Posted April 24, 2007 phpMyAdmin is a great tool, you should check it out Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-236945 Share on other sites More sharing options...
Chris.P Posted April 24, 2007 Author Share Posted April 24, 2007 Yeah I'm using phpmyadmin, its easy to use the basics although useless if you don't know what everything does, which seems to be my problem. Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-236950 Share on other sites More sharing options...
cmgmyr Posted April 24, 2007 Share Posted April 24, 2007 if you go into a table (structure) you can use the buttons on the left of each column to set them as different indexes Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-236952 Share on other sites More sharing options...
Chris.P Posted April 24, 2007 Author Share Posted April 24, 2007 Right, I'm confused now. I can see how to set an index on a field although I can't see how this helps me to link the tables through a reference id. Like I said in my first post I have two tables like this: User Table ImageTable ID ID Name Location DOB Size User table is populated every time somebody signs up through the sign up form, Image table is not. So obviously I want the ID's of both tables to stay the same so I can reference them when the Image table is updated. ImageTable needs to reference the User table so which ID field do I index? And how does Imagetable know to look in UserTable for its ID? I know how I would do this in Oracle with primary and foreign keys although I see MySQL is slightly different and I don't understand this indexing business to well. ??? ??? Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-236965 Share on other sites More sharing options...
bubblegum.anarchy Posted April 24, 2007 Share Posted April 24, 2007 Just think of indexing the same way as foreign keys are applied in oracle with the difference being that there are no actual cascading rules implemented, unless the innoDB storage engine is used in the table create statement. Using the index on what would normally be a foreign key in oracle just improves the linking speed of joined tables in mysql. Quote Link to comment https://forums.phpfreaks.com/topic/47862-setting-foreign-keys/#findComment-237443 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.