Error 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

8,4326 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

13.9k4 gold badges40 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

99k45 gold badges363 silver badges469 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,4326 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 am trying to add foreign key on my other table but this gave me error
#1072 - Key column 'role_id' doesn't exist in table

I have created a table named role

then I created like this

create table role (
  role_id varchar(15)
  primary key (role_id)
)

then when I try to alter table on my user table

alter table user
add foreign key (role_id)
references role(role_id)

and I got an error like this

#1072 - Key column 'role_id' doesn't exist in table

LoopsGod

3702 silver badges11 bronze badges

asked Sep 20, 2013 at 12:19

You have to have the column you reference in add foreign key (role_id) inside your user table. Otherwise you get that error.

You would have to have inside your user table something like:

create table user(
  ...
  role_id varchar(15)
  ...
)

Or if you don’t have it, you have to do:

ALTER TABLE user ADD COLUMN role_id VARCHAR(15)

Before you set it as a foreign key.

Nico Haase

10.8k35 gold badges41 silver badges62 bronze badges

answered Sep 20, 2013 at 12:22

Filipe SilvaFilipe Silva

20.9k5 gold badges50 silver badges67 bronze badges

You need to add role_id inside your new table also then set it to foreign key.

alter table user
roll_id varchar(15),
add foreign key (role_id)
references role(role_id)

Edit: I don’t know the proper syntax since I am new to DBMS but I had a similar error so just declare role_id again in the new table and it should work.

answered Mar 19, 2021 at 6:23

The Table user does not have the column role_id

answered Sep 20, 2013 at 12:25

BesnikBesnik

6,3731 gold badge30 silver badges33 bronze badges

Источник

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)

Top Experts
Last 24hrs	This month

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

Источник

Issue

I’m trying to write a laravel database migration but I’m getting the following error about a foreign key:

  [IlluminateDatabaseQueryException]                                                                                                                                                                                                                    
  SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'category_id' doesn't exist in table (SQL: alter table `subcategories` add constraint subcategories_category_id_foreign foreign key (`category_id`) references `categories` (`id`))  



  [PDOException]                                                                                           
  SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'category_id' doesn't exist in table

The categories and subcategories tables do get created but the foreign key doesn’t. Here’s my migration:

<?php

use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateCategoryTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function ($table) {
            $table->increments('id')->unsigned();
            $table->string('name')->unique();
        });

        Schema::create('subcategories', function ($table) {
            $table->increments('id')->unsigned();
            $table->foreign('category_id')->references('id')->on('categories');
            $table->string('name')->unique();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('categories');
        Schema::drop('subcategories');
    }
}

Any ideas? Thanks!

Solution

You should create column before creating a foreign key:

$table->integer('category_id')->unsigned();
$table->foreign('category_id')->references('id')->on('categories');

Documentation: http://laravel.com/docs/5.1/migrations#foreign-key-constraints

Answered By – Limon Monte

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Источник

Solution 1

Solution 3

Issue

Solution

Читайте также: