Cannot truncate table referenced in a foreign key constraint?

[trainoasis]

Heyo, I'm using a simple excel file with two tables and importing worked, until I added constraint to one of the tables' column with a cascade onDelete:

Articles table for example:

$table->foreignId('user_id')->constrained()->onDelete('cascade');

When running seeders this pops up:

Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (mytable.articles, CONSTRAINTarticles_user_id_foreignFOREIGN KEY (user_id) REFERENCESmytable .users(id)) (SQL: truncate tableusers)

I'm sure I'm missing something simple?

I'm only running

$this->call([
    SpreadsheetSeeder::class,
]);

and no other seeders.

[bfinlay]

What database are you using?

For MySQL, the truncation code calls SET FOREIGN_KEY_CHECKS = 0; to resolve that problem.

What version of Laravel are you using?

[bfinlay]

If you attach the migrations and spreadsheet (or a stripped down version) I can test it to see what is going on.

[trainoasis]

Hey, I'm on Laravel 8 using MySQL. Will prepare a stripped excel version & migrations that fail in an hour or so.

[trainoasis]

Here's excel & migrations attached below. Perhaps I'm expecting

$table->foreignId('user_id')->constrained()->onDelete('cascade');

to work differently, not understanding it as it should be?

migrations.zip excel_seed_dummy.xlsx

By the way, is it possible to seed only one worksheet from excel via artisan db:seed somehow?

[bfinlay]

Thanks I will take a look at this in a few hours.

By the way, is it possible to seed only one worksheet from excel via artisan db:seed somehow?

not currently but it seems like a potentially good idea.

In our usage so far, whenever we need to seed we just reseed the whole workbook (all the sheets), so I haven't added a feature like that yet.

[bfinlay]

The code to disable foreign key checks was from the legacy csv-seeder code and was not currently being used. I fixed that and added a setting truncateIgnoreForeign which is "true" by default, so it will ignore foreign key constraints when truncating.

I have added a phpunit test for this using SQLite.

Give it a try and let me know if it works for you.

[bfinlay]

By the way, is it possible to seed only one worksheet from excel via artisan db:seed somehow?

I added an artisan xl:seed command that allows you to seed individual worksheets. See the documentation.

[trainoasis]

Both additions work as expected! Thank you mate, that's great work and a great package.

PS: I just tried Laravel for fun the other day on a short campaign project so as to learn something new; it was a breeze to use and this package was something I required - worked out of the box too! Now that you added a new feature so quickly, I'm inspired to work on packages and open source stuff more. Such a good feeling man. Thanks again.

[franvilladaa]

What database are you using?

For MySQL, the truncation code calls SET FOREIGN_KEY_CHECKS = 0; to resolve that problem.

What version of Laravel are you using?

Hi, I'm having the same error but in my case I'm using SQL Server.

I don't have an onDelete() it is just a simple migration that was working on MySQL and now in SQL Server it is throwing that error

[bfinlay]

Thanks for the comment.

I will have to setup an SQL Server container to debug.

Could you attach the migrations and excel file?

[bfinlay]

database containers are setup and truncation tests are in progress.

[bfinlay]

What database are you using? For MySQL, the truncation code calls SET FOREIGN_KEY_CHECKS = 0; to resolve that problem. What version of Laravel are you using?

Hi, I'm having the same error but in my case I'm using SQL Server.

I don't have an onDelete() it is just a simple migration that was working on MySQL and now in SQL Server it is throwing that error

sqlserver issues will be addressed in #18

[bfinlay]

For SQLite, MySQL, and Postgres, v3.3.0 fixes this issue and v3.3.1 completes the tests for this issue.

For SQLServer, see #18.

Closing this issue.