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.

3

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.

1

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

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy