'Unable to create Foreign Key (ERROR 1072)

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.

mysqlsqlforeign-keysprimary-key


Solution 1:[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:[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

Solution 3:[3]

You're trying to define as FOREIGN KEY, a column which is not present on your query.

That's the reason why you are receiving Key column 'user_id' doesn't exist in table

Observe your query:

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

As the other answers have demonstrated:

you have to define the creation of the column of your new table, which will be a FK for a PK from another table

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

Solution 4:[4]

Create table attendance:

CREATE TABLE tbl_attendance
(
attendence_id INT(100) NOT NULL,
presence varchar(100) NOT NULL,
reason_id varchar(100) NULL,
PRIMARY KEY (attendance_id)
);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Fabian Barney
Solution 2 Leniel Maccaferri
Solution 3 ivanleoncz
Solution 4 Pranav Hosangadi

Related Questions