mysql link two tables via field

Zaphan58

Zaphan58

Zaphan58

Associate
Joined
30 Nov 2003
Posts
1,614
I asked this on another forum but I don't seem to be getting an answer I can understand.

From what I have read you can't use foreign keys in mysql and was told indexing what would normally be a foreign key would do the job.

I really don't see how this can work as when I index how does it know that the primary key is where it should get its data?

Here's the original question:

I have two tables one with user info inside it and one with details about images uploaded, obviously when a user logs in I want to pull 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 is primary in User table and foreign in Image Table. ID in user table auto increments every time somebody signs up. At the moment ID in ImageTable is empty.
 
Last edited:
Yeah I know that's the problem. :D

In oracle I would set ID in the image table as foreign to the ID in the UserTable so they both stay the same and I can see which Image is associated with which User. Although I don't know how to do that in MySQL.
 
I just told you how to do it. When you insert the user, also insert the image record with the user ID. When you delete a user, also delete the image record. When a user picks a new image then use the user ID from memory to update the image record.
 
I'm not sure where your problem is. A foreign key constraint will ensure referential integrity and stop inconsistencies like incorrect userids in the Image table or images left for deleted users. As long as your code is well behaved it doesn't really matter if there or not.

I assume there will only be one image per user? If more you would be better off finding some uniqueness to identify an image Either a composite primary key or a unique ID for each image.

As for creating the rows do what pinkaardvark says and create the user first then use the ID when inserting into the ImageTable.

You can then
SELECT u.ID, u.Name, i.Location
FROM UserTable u, ImageTable i
WHERE u.ID = i.ID
 
Cheers guys, I just thought there would be a way of joining the tables through MySQL like in Oracle. I have done it the way you suggested pinkaardvark and it seems to be working fine. :)
 
Zaphan58 said:
Cheers guys, I just thought there would be a way of joining the tables through MySQL like in Oracle. I have done it the way you suggested pinkaardvark and it seems to be working fine. :)
Click to expand...

You can do the same in SQL server in the database diagraming tool. However creating a join just creates a constraint. I've not used oracle but I can't see why that would be any different. You should be able to create similar constraints to enforce ref integrity in MySQL though.
 
You must log in or register to reply here.
Back
Top Bottom