Level 16
How about writing the reverse of it in down() function:
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('display_name', 'name');
});
Apr 19, 2019
7
Level 14
Renaming a column in SQLite on a table referenced by a foreign key fails
I'm trying to add a username column to my users table (which currently just has the standard columns provided in a new Laravel project on it). To avoid confusion with the name column, I wanted to first rename the name column to display_name.
I attempted to do so using the following migration:
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('name', 'display_name');
});
}
Which results in the following exception:
SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (SQL: DROP TABLE users)
I was immediately concerned that I coded something wrong, but upon further inspection, this appears to be the standard for SQLite. Here's the SQL the migration is trying to run:
CREATE TEMPORARY TABLE __temp__users AS
SELECT
id,
name,
email,
email_verified_at,
remember_token,
created_at
updated_at,
password
FROM users
DROP TABLE users
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
email VARCHAR(255) NOT NULL COLLATE BINARY,
email_verified_at DATETIME DEFAULT NULL,
remember_token VARCHAR(255) DEFAULT NULL COLLATE BINARY,
created_at DATETIME DEFAULT NULL,
updated_at DATETIME DEFAULT NULL,
password VARCHAR(60) DEFAULT NULL COLLATE BINARY,
display_name VARCHAR(255) NOT NULL
)
INSERT INTO users (
id,
display_name,
email,
email_verified_at,
remember_token,
created_at,
updated_at,
password
)
SELECT
id,
name,
email,
email_verified_at,
remember_token,
created_at,
updated_at,
password
FROM __temp__users
DROP TABLE __temp__users
This is basically creating a temp table with the new column, dropping the original table, then recreating the table, and filling in the data. While this would technically get the job done, this also ignores all issues regarding foreign keys.
I understand that there's probably ways around this (such as just creating a new column and dropping the original, or disabling foreign key constraints for migration), but I haven't seen any mentioning of this requirement on the Laravel documentation, and I'm currently under the impression that what I'm doing is supposed to work without any gimmicks.
Am I doing something incorrectly here, or is this a bug?
Level 14
@nomguy That would have the same issue. Seeing as I can't even get this migration to go through, due to this exception, I'm not even at a point where I can run the down code.
Level 14
I also just tried changing my migration code to this:
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::transaction(function() {
Schema::disableForeignKeyConstraints();
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('name', 'display_name');
});
Schema::enableForeignKeyConstraints();
});
}
And I'm getting the same exception. How am I supposed to rename columns in SQLite?
Level 16
Use it like this then:
public function up()
{
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('name', 'display_name');
});
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
}
And don't forget to write
use DB;
at the top. @tylernathanreed
Level 14
I tried that first, and it didn't work. I figured I'd try Laravel's equivalent, just in case that did something else, but that didn't work either.
:/
Level 14
This is still a problem for me. Does anyone have any ideas?
Level 1
In my case (using Laravel 6.x and SQLite) the following works for changing a table where no simple ALTER TABLE statement works (see https://www.sqlitetutorial.net/sqlite-alter-table/ for examples where this applies, it includes renaming an dropping columns):
Be aware that your .env should have the setting
DB_FOREIGN_KEYS=true
Given that there maybe other users like me who use SQLite for testing but MySQL for production, I also added a check to ensure this is only done for SQLite (as this should not be an issue for MySQL).
In general, you need to use functions Schema::disableForeignKeyConstraints() and Schema::enableForeignKeyConstraints() as these will ensure the use of the database-specific commands. The statement DB::statement('SET FOREIGN_KEY_CHECKS=0;') only works for MySQL, for SQLite you would have to use DB::statement('PRAGMA foreign_keys=off;'). By using the generic commands, Laravel abstract from these peculiarities.
Interestingly, the code below only works if you do not include the enabling/disabling of the constraints into the transaction.
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class SampleMigration extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
if ('sqlite' === DB::connection()->getDriverName()) {
Schema::disableForeignKeyConstraints();
}
DB::transaction(function() {
Schema::table('products', function (Blueprint $table) {
$table->dropColumn(['removeme']);
});
});
if ('sqlite' === DB::connection()->getDriverName()) {
Schema::enableForeignKeyConstraints();
}
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
if ('sqlite' === DB::connection()->getDriverName()) {
Schema::disableForeignKeyConstraints();
}
DB::transaction(function() {
Schema::table('products', function (Blueprint $table) {
$table->json('removeme')->nullable();
});
});
if ('sqlite' === DB::connection()->getDriverName()) {
Schema::enableForeignKeyConstraints();
}
}
Please or to participate in this conversation.