Ошибка 1072 mysql - Не ошибается лишь тот, кто ничего не делает!

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

8,7906 gold badges56 silver badges48 bronze badges

asked Jun 30, 2012 at 23:07

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

14k5 gold badges39 silver badges60 bronze badges

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

99.8k46 gold badges367 silver badges478 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

ivanleonczivanleoncz

8,7906 gold badges56 silver badges48 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)
);

answered Jul 29, 2020 at 14:52

Источник

I’ve searched for answers might work, but didn’t fix.

I’ve created tables like this and when created table ‘hobbies’ got error 1072.

CREATE  TABLE IF NOT EXISTS `project_r`.`user` (
  `user_id` INT NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `RealName` VARCHAR(45) NOT NULL ,
  `FamilyName` VARCHAR(45) NOT NULL ,
  `birthdate` VARCHAR(45) NOT NULL ,
  `Homecity` VARCHAR(45) NOT NULL ,
  `school_id` INT NOT NULL ,
  `school_enteryear` INT NOT NULL ,
  `email` VARCHAR(45) NULL ,
  `activestat` VARCHAR(45) NULL ,
  `onlinestat` VARCHAR(45) NULL ,
  `regtime` DATETIME NOT NULL ,
  `avatar` VARCHAR(45) NULL ,
  `status` VARCHAR(45) NULL ,
  `desc` VARCHAR(45) NULL ,
  `self_comment` VARCHAR(45) NULL ,
  `userMsg_id` INT NOT NULL ,
  PRIMARY KEY (`user_id`) ,
  INDEX `fk_user_school1_idx` (`school_id` ASC) ,
  CONSTRAINT `fk_user_school1`
    FOREIGN KEY (`school_id` )
    REFERENCES `project_r`.`school` (`school_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

then and create table ‘hobbies’

CREATE  TABLE IF NOT EXISTS `project_r`.`hobbies` (
  `hobby_user_id` INT NOT NULL ,
  `favorite_music` VARCHAR(45) NULL ,
  `favorite_game` VARCHAR(20) NULL ,
  `favorite_film` VARCHAR(45) NULL ,
  `favorite_book` VARCHAR(45) NULL ,
  `favorite_sport` VARCHAR(45) NULL ,
  `favorite_cartoon` VARCHAR(45) NULL ,
  `hobby` VARCHAR(45) NULL ,
  PRIMARY KEY (`user_id`) ,
  INDEX `fk_user_id` (`hobby_user_id` ASC) ,
  CONSTRAINT `fk_user_id`
    FOREIGN KEY (`hobby_user_id` )
    REFERENCES `project_r`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

both AUTO-INCREMENT things added or not or index added to referenced tables didn’t work.
thx for help.

Источник

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

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

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

Источник

I’m pretty new to SQL and I am trying to create a recipe database. Currently I have 2 tables set up that I am trying to join with a foreign key but I keep getting an error when trying to add the key.

First table:

CREATE TABLE recipe(
recipeID INT UNSIGNED NOT NULL AUTO_INCREMENT,
recipeName VARCHAR(255),
created TIMESTAMP,
updated TIMESTAMP,
PRIMARY KEY (recipeID)
);

Second table:

CREATE TABLE instructions (
instructionsID INT UNSIGNED NOT NULL AUTO_INCREMENT,
specification LONGTEXT,
listOrder INT,
PRIMARY KEY (instructionsID),
FOREIGN KEY (recipeID) REFERENCES recipe(recipeID)
);

Error:
ERROR 1072 (42000): Key column ‘recipeID’ doesn’t exist in table

recipe Table info:

Field	Type	Null	Key	Default	Extra
recipeID	int(10) unsigned	NO	PRI	NULL	auto_increment
recipeName	varchar(255)	YES		NULL
created	timestamp	NO		current_timestamp()	on update current_timestamp()
updated	timestamp	NO		0000-00-00 00:00:00

instructions Table info:

Field	Type	Null	Key	Default	Extra
instructionsID	int(10) unsigned	NO	PRI	NULL	auto_increment
specification	longtext	YES		NULL
listOrder	int(11)	YES		NULL

>Solution :

FOREIGN KEY (recipeID) REFERENCES recipe(recipeID)

Here you are referencing recipeID from recipe to recipeID in instructions table. You don’t have a recipeID in instructions table.I think you need to create recipeID column first and then reference it with recipeID in recipe table

 CREATE TABLE instructions (
    instructionsID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    specification LONGTEXT,
    listOrder INT,
    recipeID INT UNSIGNED,
    PRIMARY KEY (instructionsID),
    FOREIGN KEY (recipeID) REFERENCES recipe(recipeID)
    );

THis will work i guess.

Источник