CODEWITHSUNDEEP
phpmysqlsqlitelaravel-5
Vladimir Stus
Vladimir Stus

Reputation: 235

Laravel migration with SQLite

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?

Upvotes: 2

Views: 4500

Answers (1)

Jeff Lambert
Jeff Lambert

Reputation: 24661

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:

  1. Rename the table
  2. Recreate the table with an updated structure (including your FK constraint)
  3. Populate new table with existing data, providing values for new fields
  4. 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.

Upvotes: 5

Related Questions