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