How do I create a MySQL table when it has two foreign keys?

Question

What would be the code for creating a table with two foreign keys?

I have a USER table and a PICTURE table. Since a USER can be in many PICTURE and many PICTURE can be from a USER, I need a third table with both primary keys.

Thank you SO, as usual you are invaluable for a learning novice. :)

Answer 1

I can't speak specifically for mySQL but in most databases I have worked with you can put as many foreign keys as you need on a table. But you can only have one primary key. A third table with both keys is the right choice. Make a foreign key to each of the other two tables and a primary key consisting of both ids in the table.

Answer 2

If I understood correctly, you may need to do something like the following:

CREATE TABLE users (
    user_id  INT NOT NULL PRIMARY KEY,
    name     VARCHAR(50) NOT NULL
) ENGINE=INNODB;

CREATE TABLE pictures (
    picture_id  INT NOT NULL PRIMARY KEY,
    filename    VARCHAR(255) NOT NULL,
    posted_by   INT NOT NULL,
    FOREIGN KEY (posted_by) REFERENCES users(user_id)    
) ENGINE=INNODB;

CREATE TABLE users_in_pictures (
    user_id     INT NOT NULL,
    picture_id  INT NOT NULL,
    PRIMARY KEY (user_id, picture_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),    
    FOREIGN KEY (picture_id) REFERENCES pictures(picture_id)    
) ENGINE=INNODB;

Note that each picture can be posted by a user. In fact the posted_by field is constrained by a foreign key that references the users table.

In addition, I assume that you want to tag pictures ala-facebook. In this case, you can use the third table, which is using a composite primary key on (user_id, picture_id) and both fields are also constrained to the appropriate table.