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

Question

What will be the code to create a table with two foreign keys?

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

Thank you, as usual, you are invaluable for beginners. :)

Answer 1

If I understood correctly, you might 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;

Please note that each image can be submitted by the user. In fact, the field is posted_bylimited by a foreign key that refers to the table users.

, , ala-facebook. , (user_id, picture_id), .

Answer 2

mySQL, , , , . . - . , .