How can I make a primary key in a table to be forign key in another table?

[saidbakr]

hi
How can I make a primary key in a table to be a forign key in another table in MySQL? using ms access this is very simple, So please tell me how can this be handeled using MySQL?🙁

[e_covert]

Without a little more information on the kind of data you are using it is a little difficult to say, but here is an example of what I think you need:

usersTable: user_id (primary key), user_name
messagesTable: message_id (primary key), user_id, message

The thing to note is that mysql doesn't provide you with the same kind of automation as access. A 'foreign' key is just in how you use the data. In the above case, you would make sure that you always put a valid user_id into the messages table. Then when creating queries, you would join the tables on that user_id:

SELECT u.user_name, m. message FROM usersTable AS u, messagesTable AS m WHERE u.user_id=m.user_id;

Hope this helps...

[lukemedway]

How is it you can do that though, because you can't have two auto-incrementing fields in one table?

[localhost]

user_id doesnt have to be autoincrement
in messages table only message_id

[carrot]

you need to use INNODB table type instead of MYISAM.