Ошибка 1072 sql

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.

ivanleoncz's user avatar

ivanleoncz

9,1707 gold badges57 silver badges49 bronze badges

asked Jun 30, 2012 at 23:07

user837208's user avatar

3

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.

answered Jun 30, 2012 at 23:18

Fabian Barney's user avatar

Fabian BarneyFabian Barney

14.2k5 gold badges41 silver badges61 bronze badges

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

answered Jun 30, 2012 at 23:18

Leniel Maccaferri's user avatar

Leniel MaccaferriLeniel Maccaferri

100k46 gold badges371 silver badges480 bronze badges

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;

answered Mar 11, 2019 at 20:05

ivanleoncz's user avatar

ivanleonczivanleoncz

9,1707 gold badges57 silver badges49 bronze badges

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

Pranav Hosangadi's user avatar

answered Jul 29, 2020 at 14:52

Suhail Rojah's user avatar

1

Источник

See more:

Hey guys I need your help creating these SQL’s, I don’t know why I am running into this trouble. it creates the first 2 tables, but when it gets to the Driver table, it throws a fit
Error:

MySQL said: Documentation
#1072 — Key column ‘UserID’ doesn’t exist in table

CREATE TABLE User (
UserID int(11) NOT NULL AUTO_INCREMENT,
FirstName varchar(50) default NULL,
LastName varchar(50) default NULL,
Email varchar(50) default NULL,
Password varchar(50)default NULL,
PRIMARY KEY (UserID)
)ENGINE=INNODB;


CREATE TABLE Car (
CarID int(11) NOT NULL AUTO_INCREMENT,
Make varchar(50) default NULL,
Year int(11) default NULL,
Color varchar(50) default NULL,
PRIMARY KEY (CarID)
)ENGINE=INNODB;

CREATE TABLE Driver (
DriverID int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (DriverID),
FOREIGN KEY (UserID) REFERENCES User (UserID),
FOREIGN KEY (CarID) REFERENCES Car (CarID)
)ENGINE=INNODB;

CREATE TABLE Request (
RequestID int(11) NOT NULL AUTO_INCREMENT,
From varchar(50) default NULL,
To varchar(50) default NULL,
RequestDate Date default NULL,
PRIMARY KEY (RequestID),
FOREIGN KEY (UserID) REFERENCES User (UserID),
FOREIGN KEY (CarID) REFERENCES Car (CarID)
)ENGINE=INNODB;

Thanks for your help.

Solution 1

You are trying to set up a Foreign key on a column that you have not yet created. Try this …

CREATE TABLE Driver (
DriverID int(11) NOT NULL AUTO_INCREMENT,
UserID int,
CarID int,
PRIMARY KEY (DriverID),
FOREIGN KEY (UserID) REFERENCES User (UserID),
FOREIGN KEY (CarID) REFERENCES Car (CarID)
)ENGINE=INNODB;

Solution 3

you just write id in the primary key not write userid, carid

Comments

Solution 4

you just write id int in primary number you change the #id as #userid then work by:-shivam mandal

Comments

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

 

Print

Answers RSS

Top Experts
Last 24hrs This month

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900

Источник

heres my input into the command line you can see I tried to add a foreign key in different ways and keep getting the same error what am I doing wrong?

mysql> create table membership(

-> m_no char(3) primary key,
-> m_fname varchar(15) not null,
-> m_lname varchar(15) not null,
-> m_street varchar(30) not null,
-> m_city varchar(20) not null,
-> m_st char(2) not null,
-> m_balance varchar(3));

Query OK, 0 rows affected (1.06 sec)

mysql> create table rental(

-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership(m_no));
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql> create table rental(
-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership);
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql> create table rental(
-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership)
-> engine=innodb;
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table


mysql> create table rental(

-> r_no char(4) primary key,
-> r_date date not null,
-> foreign key (m_no) references membership(m_no))
-> engine=innodb;
ERROR 1072 (42000): Key column 'm_no' doesn't exist in table

mysql> create table rental(

-> r_no char(4) primary key,
-> r_date date not null);

Query OK, 0 rows affected (0.22 sec)

mysql> alter table rental add foreign key (m_no) references membership(m_no);

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

mysql>

Источник

Перейти к контенту

See more:

Hey guys I need your help creating these SQL’s, I don’t know why I am running into this trouble. it creates the first 2 tables, but when it gets to the Driver table, it throws a fit
Error:

MySQL said: Documentation
#1072 — Key column ‘UserID’ doesn’t exist in table

CREATE TABLE User (
UserID int(11) NOT NULL AUTO_INCREMENT,
FirstName varchar(50) default NULL,
LastName varchar(50) default NULL,
Email varchar(50) default NULL,
Password varchar(50)default NULL,
PRIMARY KEY (UserID)
)ENGINE=INNODB;


CREATE TABLE Car (
CarID int(11) NOT NULL AUTO_INCREMENT,
Make varchar(50) default NULL,
Year int(11) default NULL,
Color varchar(50) default NULL,
PRIMARY KEY (CarID)
)ENGINE=INNODB;

CREATE TABLE Driver (
DriverID int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (DriverID),
FOREIGN KEY (UserID) REFERENCES User (UserID),
FOREIGN KEY (CarID) REFERENCES Car (CarID)
)ENGINE=INNODB;

CREATE TABLE Request (
RequestID int(11) NOT NULL AUTO_INCREMENT,
From varchar(50) default NULL,
To varchar(50) default NULL,
RequestDate Date default NULL,
PRIMARY KEY (RequestID),
FOREIGN KEY (UserID) REFERENCES User (UserID),
FOREIGN KEY (CarID) REFERENCES Car (CarID)
)ENGINE=INNODB;

Thanks for your help.

Solution 1

You are trying to set up a Foreign key on a column that you have not yet created. Try this …

CREATE TABLE Driver (
DriverID int(11) NOT NULL AUTO_INCREMENT,
UserID int,
CarID int,
PRIMARY KEY (DriverID),
FOREIGN KEY (UserID) REFERENCES User (UserID),
FOREIGN KEY (CarID) REFERENCES Car (CarID)
)ENGINE=INNODB;

Solution 3

you just write id in the primary key not write userid, carid

Comments

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Print

Answers RSS

Top Experts
Last 24hrs This month

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900

Я продолжаю получать ошибку 1072 при попытке добавить атрибут внешнего ключа и привязать мой атрибут к другой таблице. Я пробовал разные способы записи, но я продолжаю получать сообщение о том, что «department_id не существует в таблице». Если бы вы могли дать мне руку, которая была бы потрясающей! Огромное спасибо!

Вот мой код:

Alter table employee
add constraint department_fk
foreign key ( department_id)
references department(department_id);

И вот остальные мои таблицы:

Create table Department (
    department_id integer auto_increment primary key not null,
    department_name varchar (50) not null,
    Office_number varchar(50) not null,
    phone char (20) not null
);


Create table employee (
    employee_id integer auto_increment primary key not null,
    first_name varchar (25) not null,
    last_name varchar (25) not null,
    phone char(20) not null,
    email varchar (100) not null,
    salary decimal (10,2)
);

Create table project (
    project_id integer auto_increment primary key not null,
    max_hours time not null,
    start_date datetime not null,
    end_date datetime not null,
    Project_lead integer,
    Constraint project_fk1 
    foreign key (employee_id)
    references employee(employee_id),
    Department_id integer,
    Constraint project_fk2 
    foreign key (department_id) 
    references department (department_id)  
);

Create table assignment (
    employee_id integer not null,
    project_id integer not null,
    hours_worked time not null,
    primary key (employee_id, project_id),
    constraint assignment_fk1 
    foreign key(employee_id) 
    references employee(employee_id),
    constraint assignment_fk2 
    foreign key (project_id) 
    references project(project_id)
);

Alter table project
drop foreign key department_id;

Заранее спасибо!

У меня есть таблица, которая выглядит так: 

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

Я пытаюсь создать новую таблицу следующим образом: 

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

Но я получаю эту ошибку:

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

Я уверен, что мне не хватает чего-то очень простого.

4 ответы

Попробуй это:

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

Первый user_id во внешнем ключе ограничение относится к таблице, в которой определено ограничение, а второе относится к таблице, на которую оно указывает. Итак, вам нужно поле user_id в вашей таблице вакансий тоже.

Создан 01 июля ’12, 00:07

Это скрипт, который вам нужен:

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

Вот хороший справочник для изучения основ настройки отношений: Ограничение ВНЕШНЕГО КЛЮЧА SQL

Создан 01 июля ’12, 00:07

Вы пытаетесь определить как FOREIGN KEY столбец, которого нет в вашем запросе.

Вот почему вы получаете Key column 'user_id' doesn't exist in table

Наблюдайте за своим запросом:

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

Как показали другие ответы:

вам нужно определить создание столбца вашей новой таблицы, который будет FK для ПК из другой таблицы

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

ответ дан 11 мар ’19, в 20:03

Создать посещаемость за столом:

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

Создан 29 июля ’20, 18:07

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

mysql
sql
foreign-keys
primary-key

or задайте свой вопрос.

I have researched thoroughly before asking this question including on this site.

I have a students table:

CREATE TABLE IF NOT EXISTS students(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
middle_name VARCHAR(20),
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
password CHAR(40) NOT NULL,
reg_date DATETIME NOT NULL,
PRIMARY KEY (student_id),
UNIQUE (email));

I also have a subjects table:

CREATE TABLE IF NOT EXISTS subjects(
subject_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
subject_name VARCHAR(40) NOT NULL,
level_of_entry VARCHAR(20) NOT NULL,
exam_board VARCHAR(20) NOT NULL,
PRIMARY KEY (subject_id));

I am now creating a table to link the above tables:

CREATE TABLE IF NOT EXISTS entries(
exam_date DATETIME NOT NULL,
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id)
);

My problem is that when I try to declare the foreign keys in the third table called entries, I get an error stating that the subject_id foreign key is not in the table referenced.
ERROR 1072 (42000) : Key column ‘student_id’ doesn’t exist in table, even though it is clearly contained inside the the students table and the same applies to ‘subject_id’ and the subject table.
I am certain that my syntax for declaring the foreign keys is correct so I am unsure how to fix the problem.
All help is appreciated. Thank you.

Answer 1

You forgot to create these two columns before applying your foreign key constraints :

CREATE TABLE IF NOT EXISTS entries(
exam_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
subject_id INT UNSIGNED NOT NULL,
exam_date DATETIME NOT NULL,
PRIMARY KEY (exam_id),
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (subject_id) REFERENCES subjects (subject_id)
);

EDIT :

I advise you to add in every table a unique ID column (here : exam_id).

Источник

У меня есть таблица пользователей, которая выглядит так:

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

Я пытаюсь создать новую таблицу, такую ​​как this-

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

но я получаю ошибку — ERROR 1072 (42000): Key column 'user_id' doesn't exist in table

Я уверен, что мне не хватает чего-то очень простого. Пожалуйста, предложите, что может быть неправильным.

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 1072 mysql
  • Ошибка 1066 02 рено премиум
  • Ошибка 1070 сразу после запуска служба зависла
  • Ошибка 1065 ниссан теана j31
  • Ошибка 1065 ниссан альмера классик