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.

4 Answers

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.

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

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; 

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) );