Rails 6 add_foreign_key & remove_foreign_key SQLite3

Rails provides add_foreign_key to add foreign key constraint for a column on a table.

It also provides remove_foreign_key to remove the foreign key constraint.

Before Rails 6, add_foreign_key and remove_foreign_key were not supported for SQLite3.

Rails 6 now adds this support. Now, we can create and remove foreign key constraints using add_foreign_key and remove_foreign_key in SQLite3.

Let's checkout how it works.

Rails 5.2

We have two tables named as orders and users. Now, let's add foreign key constraint of users in orders table using add_foreign_key and then try removing it using remove_foreign_key.

>> class AddUserReferenceToOrders < ActiveRecord::Migration[6.0]
>>   def change
>>     add_column :orders, :user_id, :integer
>>     add_foreign_key :orders, :users
>>   end
>> end

=> :change

>> AddUserReferenceToOrders.new.change
-- add_column(:orders, :user_id, :integer)
   (1.2ms)  ALTER TABLE "orders" ADD "user_id" integer
   -> 0.0058s
-- add_foreign_key(:orders, :users)
   -> 0.0000s

=> nil

>> class RemoveUserForeignKeyFromOrders < ActiveRecord::Migration[6.0]
>>   def change
>>     remove_foreign_key :orders, :users
>>   end
>> end

=> :change

>> RemoveUserForeignKeyFromOrders.new.change
-- remove_foreign_key(:orders, :users)
   -> 0.0001s

=> nil

We can see that add_foreign_key and remove_foreign_key are ignored by Rails 5.2 with SQLite3.

Rails 6.0.0.rc1

We have two tables named as orders and users. Now, let's add foreign key constraint of users in orders table using add_foreign_key.

>> class AddUserReferenceToOrders < ActiveRecord::Migration[6.0]
>>   def change
>>     add_column :orders, :user_id, :integer
>>     add_foreign_key :orders, :users
>>   end
>> end

=> :change

>> AddUserReferenceToOrders.new.change
-- add_column(:orders, :user_id, :integer)
   (1.0ms)  SELECT sqlite_version(*)
   (2.9ms)  ALTER TABLE "orders" ADD "user_id" integer
   -> 0.0091s
-- add_foreign_key(:orders, :users)
   (0.0ms)  begin transaction
   (0.1ms)  PRAGMA foreign_keys
   (0.1ms)  PRAGMA defer_foreign_keys
   (0.0ms)  PRAGMA defer_foreign_keys = ON
   (0.1ms)  PRAGMA foreign_keys = OFF
   (0.2ms)  CREATE TEMPORARY TABLE "aorders" ("id" integer NOT NULL PRIMARY KEY, "number" varchar DEFAULT NULL, "total" decimal DEFAULT NULL, "completed_at" datetime DEFAULT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "user_id" integer DEFAULT NULL)
   (0.1ms)  INSERT INTO "aorders" ("id","number","total","completed_at","created_at","updated_at","user_id")
                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "orders"
   (0.3ms)  DROP TABLE "orders"
   (0.1ms)  CREATE TABLE "orders" ("id" integer NOT NULL PRIMARY KEY, "number" varchar DEFAULT NULL, "total" decimal DEFAULT NULL, "completed_at" datetime DEFAULT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "user_id" integer DEFAULT NULL, CONSTRAINT "fk_rails_f868b47f6a"
FOREIGN KEY ("user_id")
  REFERENCES "users" ("id")
)
   (0.1ms)  INSERT INTO "orders" ("id","number","total","completed_at","created_at","updated_at","user_id")
                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "aorders"
   (0.1ms)  DROP TABLE "aorders"
   (0.0ms)  PRAGMA defer_foreign_keys = 0
   (0.0ms)  PRAGMA foreign_keys = 1
   (0.6ms)  commit transaction
   -> 0.0083s

=> []

>> class RemoveUserForeignKeyFromOrders < ActiveRecord::Migration[6.0]
>>   def change
>>     remove_foreign_key :orders, :users
>>   end
>> end

=> :change

>> RemoveUserForeignKeyFromOrders.new.change
-- remove_foreign_key(:orders, :users)
   (1.4ms)  SELECT sqlite_version(*)
   (0.0ms)  begin transaction
   (0.0ms)  PRAGMA foreign_keys
   (0.0ms)  PRAGMA defer_foreign_keys
   (0.0ms)  PRAGMA defer_foreign_keys = ON
   (0.0ms)  PRAGMA foreign_keys = OFF
   (0.2ms)  CREATE TEMPORARY TABLE "aorders" ("id" integer NOT NULL PRIMARY KEY, "number" varchar DEFAULT NULL, "total" decimal DEFAULT NULL, "completed_at" datetime DEFAULT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "user_id" integer DEFAULT NULL)
   (0.3ms)  INSERT INTO "aorders" ("id","number","total","completed_at","created_at","updated_at","user_id")
                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "orders"
   (0.4ms)  DROP TABLE "orders"
   (0.1ms)  CREATE TABLE "orders" ("id" integer NOT NULL PRIMARY KEY, "number" varchar DEFAULT NULL, "total" decimal DEFAULT NULL, "completed_at" datetime DEFAULT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "user_id" integer DEFAULT NULL)
   (0.1ms)  INSERT INTO "orders" ("id","number","total","completed_at","created_at","updated_at","user_id")
                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "aorders"
   (0.1ms)  DROP TABLE "aorders"
   (0.0ms)  PRAGMA defer_foreign_keys = 0
   (0.0ms)  PRAGMA foreign_keys = 1
   (0.7ms)  commit transaction
   -> 0.0179s

=> []

Now, let's remove foreign key constraint of users from orders table using remove_foreign_key.

>> class RemoveUserForeignKeyFromOrders < ActiveRecord::Migration[6.0]
>>   def change
>>     remove_foreign_key :orders, :users
>>   end
>> end

=> :change

>> RemoveUserForeignKeyFromOrders.new.change
-- remove_foreign_key(:orders, :users)
   (1.4ms)  SELECT sqlite_version(*)
   (0.0ms)  begin transaction
   (0.0ms)  PRAGMA foreign_keys
   (0.0ms)  PRAGMA defer_foreign_keys
   (0.0ms)  PRAGMA defer_foreign_keys = ON
   (0.0ms)  PRAGMA foreign_keys = OFF
   (0.2ms)  CREATE TEMPORARY TABLE "aorders" ("id" integer NOT NULL PRIMARY KEY, "number" varchar DEFAULT NULL, "total" decimal DEFAULT NULL, "completed_at" datetime DEFAULT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "user_id" integer DEFAULT NULL)
   (0.3ms)  INSERT INTO "aorders" ("id","number","total","completed_at","created_at","updated_at","user_id")
                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "orders"
   (0.4ms)  DROP TABLE "orders"
   (0.1ms)  CREATE TABLE "orders" ("id" integer NOT NULL PRIMARY KEY, "number" varchar DEFAULT NULL, "total" decimal DEFAULT NULL, "completed_at" datetime DEFAULT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, "user_id" integer DEFAULT NULL)
   (0.1ms)  INSERT INTO "orders" ("id","number","total","completed_at","created_at","updated_at","user_id")
                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "aorders"
   (0.1ms)  DROP TABLE "aorders"
   (0.0ms)  PRAGMA defer_foreign_keys = 0
   (0.0ms)  PRAGMA foreign_keys = 1
   (0.7ms)  commit transaction
   -> 0.0179s

=> []

We can see here that with Rails 6, add_foreign_key and remove_foreign_key work and were able to add and remove foreign key constraint respectively.

Here is the relevant pull request.