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 the customer_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 my database.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.
2
Answers
Answer posted here: https://stackoverflow.com/a/61393131/7018549.
Use
Instead of