This blog is part of our Rails 6 series. Rails 6.0 was recently released.

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.2 ms ) ALTER TABLE "orders" ADD "user_id" integer -> 0.0058 s -- add_foreign_key ( :orders , :users ) -> 0.0000 s => 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.0001 s => 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.0 ms ) SELECT sqlite_version ( * ) ( 2.9 ms ) ALTER TABLE "orders" ADD "user_id" integer -> 0.0091 s -- add_foreign_key ( :orders , :users ) ( 0.0 ms ) begin transaction ( 0.1 ms ) PRAGMA foreign_keys ( 0.1 ms ) PRAGMA defer_foreign_keys ( 0.0 ms ) PRAGMA defer_foreign_keys = ON ( 0.1 ms ) PRAGMA foreign_keys = OFF ( 0.2 ms ) 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.1 ms ) 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.3 ms ) DROP TABLE "orders" ( 0.1 ms ) 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.1 ms ) 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.1 ms ) DROP TABLE "aorders" ( 0.0 ms ) PRAGMA defer_foreign_keys = 0 ( 0.0 ms ) PRAGMA foreign_keys = 1 ( 0.6 ms ) commit transaction -> 0.0083 s => [] >> 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.4 ms ) SELECT sqlite_version ( * ) ( 0.0 ms ) begin transaction ( 0.0 ms ) PRAGMA foreign_keys ( 0.0 ms ) PRAGMA defer_foreign_keys ( 0.0 ms ) PRAGMA defer_foreign_keys = ON ( 0.0 ms ) PRAGMA foreign_keys = OFF ( 0.2 ms ) 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.3 ms ) 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.4 ms ) DROP TABLE "orders" ( 0.1 ms ) 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.1 ms ) 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.1 ms ) DROP TABLE "aorders" ( 0.0 ms ) PRAGMA defer_foreign_keys = 0 ( 0.0 ms ) PRAGMA foreign_keys = 1 ( 0.7 ms ) commit transaction -> 0.0179 s => []

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.4 ms ) SELECT sqlite_version ( * ) ( 0.0 ms ) begin transaction ( 0.0 ms ) PRAGMA foreign_keys ( 0.0 ms ) PRAGMA defer_foreign_keys ( 0.0 ms ) PRAGMA defer_foreign_keys = ON ( 0.0 ms ) PRAGMA foreign_keys = OFF ( 0.2 ms ) 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.3 ms ) 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.4 ms ) DROP TABLE "orders" ( 0.1 ms ) 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.1 ms ) 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.1 ms ) DROP TABLE "aorders" ( 0.0 ms ) PRAGMA defer_foreign_keys = 0 ( 0.0 ms ) PRAGMA foreign_keys = 1 ( 0.7 ms ) commit transaction -> 0.0179 s => []

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.