Error code 1824 mysql workbench

When I try to create the ‘Project table I get this error Code: 1824. Failed to open the referenced table ‘Employee’.

My syntax:

CREATE DATABASE IF NOT EXISTS Test;
USE Test;

CREATE TABLE IF NOT EXISTS Customer (
CustomerID VARCHAR(7) NOT NULL,
CustomerName VARCHAR(50),
CustAdress VARCHAR(70),
CustEmail VARCHAR(50),
PRIMARY KEY (CustomerID)
);

CREATE TABLE IF NOT EXISTS Employee (
EmpID VARCHAR(7) NOT NULL,
EmpName VARCHAR(50),
Position VARCHAR(30),
EmpTimePrice INT(4),
PRIMARY KEY (EmpID)
);

CREATE TABLE IF NOT EXISTS Project (
ProjectNo VARCHAR(7),
ProjectName VARCHAR(50),
StartDate DATE,
ProjTimePrice INT(6),
CustomerID VARCHAR(7),
EmpID VARCHAR(7),
PRIMARY KEY (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID),
FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) 
);

CREATE TABLE IF NOT EXISTS ProjectWork (
ProjectNo VARCHAR(7),
EmpID VARCHAR(7),
PWDATE DATE,
HoursWorked INT(5),
FOREIGN KEY (ProjectNo) REFERENCES Project (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID)
);

The names look correct to me and I have referenced the foreign key so I don’t understand why I get this error. Any help would be appreciated, thanks.

Issue

I am building a database for a school project, but for some reason I cannon make a foreign key reference between 2 tables (only those 2). My project has 14 tables and it works fine for all the others.

The tables are made like:

create table degree(
    title varchar(50),
    idryma varchar(40),
    bathmida enum('High School', 'Univercity', 'Master', 'PHD'),
    constraint degree_id primary key (title, idryma)
);

create table has_degree(
    degree_title varchar(50),
    degree_idryma varchar(40),
    employee_username varchar(12),
    acquisition_year year(4),
    grade float(3,1),

    constraint has_degree_id primary key (degree_title, degree_idryma, employee_username)
);

And then I try to alter the table so that I make the foreign key connections:

alter table has_degree add foreign key (degree_title) references degree(title);
alter table has_degree add foreign key (degree_idryma) references degree(idryma);

But I keep on getting

Error Code: 1824. Failed to open the referenced table ‘degree’

I have tried to make them like that:

create table degree(
    title varchar(50),
    idryma varchar(40),
    bathmida enum('High School', 'Univercity', 'Master', 'PHD'),
    constraint degree_id primary key (title, idryma)
);

create table has_degree(
    degree_title varchar(50),
    degree_idryma varchar(40),
    employee_username varchar(12),
    acquisition_year year(4),
    grade float(3,1),

    foreign key (degree_title) references degree(title),
    foreign key (degree_idryma) references degree(idryma),
    
    /*employee is an other table that I use and that works just fine*/

    foreign key (employee_username) references employee(employee_username),
    constraint has_degree_id primary key (degree_title, degree_idryma, employee_username)
);

But the only thing that changes is that I get

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ‘has_degree_ibfk_2’ in the referenced table ‘degree’

Solution

The columns in your foreign key in table has_degree must be the same as the columns in the primary key of the referenced table degree.

In this case, the primary key of degree consists of two varchar columns.

So the foreign key in has_degree that references it must also be only two varchar columns, and values in those columns in has_degree must match exactly the values in a row of degree.

You defined the foreign key this way:

foreign key (degree_title) references degree(title),
foreign key (degree_idryma) references degree(idryma),

But that’s two foreign keys, each having a single column. You need one foreign key with two columns:

foreign key (degree_title, degree_idryma) references degree(title, idryma),

Answered By – Bill Karwin

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

Hello everyone, 

I am facing an issue with my table migration I am getting a weird error while running 

php artisan migrate:fresh

The error is 

 SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'blog' (SQL: alter table `blog_comments` add constraint `blog_comments_postid_foreign` foreign key (`postID`) references `blog` (`id`) on delete cascade) at C:laravelwp4worldvendorlaravelframeworksrcIlluminateDatabaseConnection.php:671 667| // If an exception occurs when attempting to run a query, we'll format the error 668| // message to include the bindings with SQL, which will make this exception a 669| // lot more helpful to the developer instead of just the database's errors. 670| catch (Exception $e) { > 671| throw new QueryException( 672| $query, $this->prepareBindings($bindings), $e 673| ); 674| } 675|

Here is the migration file 

<?phpuse IlluminateDatabaseMigrationsMigration;use IlluminateDatabaseSchemaBlueprint;use IlluminateSupportFacadesSchema;class CreateBlogsTable extends Migration{ /** * Run the migrations. * * @return void */ public function up(){ Schema::create('blog', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('title'); $table->string('post_url'); $table->string('img_path')->nullable(); $table->longtext('content'); $table->string('tags'); $table->string('status')->default(0); $table->string('public_date', 30); $table->unsignedBigInteger('ownerID'); $table->foreign('ownerID')->references('id')->on('admins')->onDelete('cascade'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('blog'); }}

Does anyone know what is the issue about? I had checked everything and there is no error or any missing thing?
Thank you in advance

System Info:

• OS: Ubuntu 19.10
• Laravel Version: 7.0
• PHP Version: 7.3.11-0ubuntu0.19.10.4
• MySQL Version: mysql Ver 8.0.19-0ubuntu0.19.10.3 for Linux on x86_64 ((Ubuntu))

I am building a small Laravel application and am having an issue with MySQL and relationships. When I try to run my migrations, this is the error I get:

SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'customers_table' (SQL: alter table `customer_contacts` add constraint `customer_contacts_customer_id_foreign` foreign key (`customer_id`) references `customers_table` (`id`))

And these are the two migration files in question.

customer table and migration:

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateCustomersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('customers', function (Blueprint $table) {
            $table->id();

            $table->string('company_name', 50);
            $table->string('phone_number', 20)->nullable();
            $table->string('fax_number', 20)->nullable();
            $table->string('address_line_1', 75)->nullable();
            $table->string('address_line_2', 75)->nullable();
            $table->string('city', 75)->nullable();
            $table->string('state', 30)->nullable();
            $table->string('zip', 11)->nullable();
            $table->string('industry', 100)->nullable();
            $table->text('notes')->nullable();

            $table->timestamps();
        });
    }

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

And thecustomer_contacts table and migration:

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class CreateCustomerContactsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('customer_contacts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('customer_id')->constrained('customers_table');
            $table->string('name', 50);
            $table->string('title', 50);
            $table->string('project', 50);
            $table->string('email', 50);
            $table->string('mobile_phone', 20);
            $table->string('work_phone', 20);
            $table->timestamps();
        });
    }

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

And this is the relevant section from mydatabase.php file:

...

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => 'InnoDB',
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

...

I tried changing my database to SQLite to see if I get the same error and I do not, only MySQL creates this error.