setting foreign keys

[Chris.P]

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. 

[Chris.P]

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! :-\

[bubblegum.anarchy]

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.

[Chris.P]

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

[bubblegum.anarchy]

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);

[Chris.P]

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

[cmgmyr]

phpMyAdmin is a great tool, you should check it out

[Chris.P]

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. 

[cmgmyr]

if you go into a table (structure) you can use the buttons on the left of each column to set them as different indexes

[Chris.P]

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.

 

??? ???  

 

[bubblegum.anarchy]

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.