Framework: Disable ForeignKeyConstraints not working with SQL Server

[michele-grifa]

• Laravel Version: 8.16.1
• PHP Version: 7.4
• Database Driver & Version: ODBC Driver 17 For SQL Server

Description:

I'm trying to truncate a table before seed and i use the following code

Schema::disableForeignKeyConstraints();
DB::table('tableName')->truncate();
Schema::enableForeignKeyConstraints();

The error that i receive is:

 SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Cannot truncate a table referenced in a foreign key constraint. (SQL: truncate table [tablename])

[driesvints]

Will need help from someone which can test this on SQL Server with the ODBC driver.

[rodrigopedra]

Per MSDN docs on TRUNCATE TABLE

You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. You can truncate a table that has a foreign key that references itself.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15#restrictions

SQL Server won't let you truncate a table that have foreign keys constraints referencing it.

The catch here is that even when the foreign key constraints checks are disabled, the foreign keys references are still there.

Disabling the checks is the same as telling SQL Server: "hey don't bother with them, we both know they are still there, but ignore them for a while". It works for DML commands where SQL looks at the data being manipulated.

But TRUNCATE TABLE is a DDL command. SQL Server won't look to the related table records, it will check the table definition and the database schema before running it. If there are foreign keys referencing that table, it will fail.

If you really need to truncate, mostly for performance reasons, for example when you have a huge table, you'll need to drop the foreign keys, truncate and then recreate those.

Another option is to use DB::table('tableName')->delete(); instead, but I know it can be much slower.

I found some threads in Stack Overflow with alternative solutions, such as making a script to drop all foreign keys and recreate them later:

https://stackoverflow.com/a/253931/1211472

https://stackoverflow.com/a/3843826/1211472

Unfortunately there is not much to do on Laravel side.

Hope this helps.

[driesvints]

Thanks for the help here (again) @rodrigopedra :)

[rodrigopedra]

You’re welcome @driesvints ! Have a nice day =)