Unable to create Foreign Key (ERROR 1072)

Question

I have a table which looks like this:

mysql>  SHOW COLUMNS FROM Users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| user_id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| username   | varchar(50)  | YES  |     | NULL    |                |
| password   | varchar(255) | YES  |     | NULL    |                |
| email      | varchar(255) | YES  |     | NULL    |                |
| phone      | varchar(255) | YES  |     | NULL    |                |

I am trying to create a new table like this:

create table jobs (id int,  FOREIGN KEY (user_id) REFERENCES Users(user_id)) ENGINE=INNODB;

But I am getting this error:

ERROR 1072 (42000): Key column 'user_id' doesn't exist in table

I am sure I am missing something very basic.

Solution 1

Try this:

create table jobs (
    id int,  
    user_id int,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
) ENGINE=INNODB;

The first user_id in foreign key constraint refers to the table where the contraint is defined and the second refers to the table where it is pointing to. So you need a field user_id in your jobs table, too.

Solution 2

This is the script you need:

CREATE TABLE jobs
(
    id int NOT NULL,
    user_id int NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
)

Here's a good reference to learn the basics about setting up relationships: SQL FOREIGN KEY Constraint