Level 122
it looks at tables in the db not previous migrations. The error message you see is direct from the database rather than what Laravel thinks is an issue
Oct 26, 2022
6
Level 9
Failed to open referenced table
I have got an error:
SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'regions' (SQL: alter table `gantt_chart_templates` add constraint `gantt_chart_templates_region_id_foreign` foreign key (`region_id`) references `regions` (`id`) on delete cascade)
It is saying it can't find the tables, however, the table is there. But, it seems the table wasn't created using migration before. Could it be the reason? Or there should be no issue as long as the table is there?
My migration is:
Schema::connection('mysql')->create('gantt_chart_templates', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('project_types');
$table->unsignedInteger('region_id');
$table->foreign('region_id')->references('id')->on('regions')->onDelete('cascade');
$table->timestamps();
});
Level 27
Have you checked that the regions table has the right engine? MyISAM doesn’t support foreign keys, for example, while InnoDB does. Laravel uses the default engine in the MySQL server unless you specify one, but you say this table wasn’t created through Laravel, so it may be that it uses MyISAM even if your default is InnoDB.
If you try to add a foreign key like this between an InnoDB table and a MyISAM table, you will often get the “failed to open the referenced table” error.
3 likes
Level 9
@kokoshneta Okay, looks like it is the issue, is it safe to change MyISAM to InnoDB on an already populated table?
Level 27
@Inquisitive Yes, perfectly safe. The differences between the two are only really to do with what features they support – the data itself is the same (or samey enough that it can be easily converted back and forth without problems). The key differences are:
- InnoDB supports foreign keys, transactions; MyISAM does not
- InnoDB does row-locking (queries lock only affected rows, so other queries can run concurrently on the same table; MyISAM does table-locking (queries lock the entire table)
- InnoDB uses transactional logs and other means to prevent or fix data corruption or loss; MyISAM doesn’t do anything to secure data integrity
- MyISAM has always supported full-text indexing; InnoDB did not until MySQL 5.6.4.
(With help from this ServerFault answer for a lot of the details.)
The MySQL website has a long and detailed list of things to look out for when migrating a table from MyISAM to InnoDB. As always, their docs are very verbose, but it’s probably a good idea to have a look through it and see if any of the caveats apply to your table.
2 likes
Level 5
The gantt_chart_templates table is created before regions table and it reference the regions table. what you need to do is change the time on the migration folder so regions table will be created before gantt_chart_templates .
eg 2023_01_13_093501_create_gantt_chart_templates_table.php, 2023_01_13_093502_create_region_table.php .
change to 2023_01_13_093503_create_region_table.php, 2023_01_13_093504_create_gantt_chart_templates_table.php.
Then run php artisan migrate:fresh to refresh your migration
1 like
Level 6
@yerowo Perfect worked for me
Please or to participate in this conversation.