Laravel migration with SQLite

Question

I have migration in Postgres and SQLite, and I am facing problem with SQLite database. Same migrations are for 2 different databases. Postgres migration is OK, but not SQlite. I added migration for altering table and adding new field.

   public function up()
   {
       Schema::table('retailer_products_photo_fix', function ($table) {

           $table->integer('currency_id')->unsigned();
           $table
               ->foreign('currency_id')
               ->references('id')
               ->on('currency')
               ->onUpdate('cascade')
               ->onDelete('cascade')
           ;

           $table->dropColumn('currency');
       });

   }

But the following error was thrown:

General error: 1 Cannot add a NOT NULL column with default value NULL

When I try to add nullable() field isn't created and when I add default value 0 or 1 I got constraint error because in related table I have rows 1 and 2. How can this be solved?

The default value for columns is null in SQLite. So I'd be more concerned about the `nullable()` not working. — Devon Bessemer, Aug 01 '16 at 15:56

score 5 · Accepted Answer · edited May 23 '17 at 12:22

Quoting from this answer by Daniel Vassallo:

SQLite doesn't support the ADD CONSTRAINT variant of the ALTER TABLE command

(The source that answer uses is here)

So attempting to alter your table by adding a foreign key constraint isn't going to work. You will need to:

Rename the table
Recreate the table with an updated structure (including your FK constraint)
Populate new table with existing data, providing values for new fields
Drop your old table

You can swap #1 and #2 so that you create a temporary version of your table, and then drop the old one / rename the new one at the very end, but that's up to you. It may be safer to go that route instead in case something goes wrong as you won't have your old table in a renamed state at that point.

Laravel migration with SQLite

1 Answers1