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.

1>> class AddUserReferenceToOrders < ActiveRecord::Migration[6.0]
2>>   def change
3>>     add_column :orders, :user_id, :integer
4>>     add_foreign_key :orders, :users
5>>   end
6>> end
7
8=> :change
9
10>> AddUserReferenceToOrders.new.change
11-- add_column(:orders, :user_id, :integer)
12   (1.2ms)  ALTER TABLE "orders" ADD "user_id" integer
13   -> 0.0058s
14-- add_foreign_key(:orders, :users)
15   -> 0.0000s
16
17=> nil
18
19>> class RemoveUserForeignKeyFromOrders < ActiveRecord::Migration[6.0]
20>>   def change
21>>     remove_foreign_key :orders, :users
22>>   end
23>> end
24
25=> :change
26
27>> RemoveUserForeignKeyFromOrders.new.change
28-- remove_foreign_key(:orders, :users)
29   -> 0.0001s
30
31=> nil
Copy

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.

1>> class AddUserReferenceToOrders < ActiveRecord::Migration[6.0]
2>>   def change
3>>     add_column :orders, :user_id, :integer
4>>     add_foreign_key :orders, :users
5>>   end
6>> end
7
8=> :change
9
10>> AddUserReferenceToOrders.new.change
11-- add_column(:orders, :user_id, :integer)
12   (1.0ms)  SELECT sqlite_version(*)
13   (2.9ms)  ALTER TABLE "orders" ADD "user_id" integer
14   -> 0.0091s
15-- add_foreign_key(:orders, :users)
16   (0.0ms)  begin transaction
17   (0.1ms)  PRAGMA foreign_keys
18   (0.1ms)  PRAGMA defer_foreign_keys
19   (0.0ms)  PRAGMA defer_foreign_keys = ON
20   (0.1ms)  PRAGMA foreign_keys = OFF
21   (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)
22   (0.1ms)  INSERT INTO "aorders" ("id","number","total","completed_at","created_at","updated_at","user_id")
23                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "orders"
24   (0.3ms)  DROP TABLE "orders"
25   (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"
26FOREIGN KEY ("user_id")
27  REFERENCES "users" ("id")
28)
29   (0.1ms)  INSERT INTO "orders" ("id","number","total","completed_at","created_at","updated_at","user_id")
30                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "aorders"
31   (0.1ms)  DROP TABLE "aorders"
32   (0.0ms)  PRAGMA defer_foreign_keys = 0
33   (0.0ms)  PRAGMA foreign_keys = 1
34   (0.6ms)  commit transaction
35   -> 0.0083s
36
37=> []
38
39>> class RemoveUserForeignKeyFromOrders < ActiveRecord::Migration[6.0]
40>>   def change
41>>     remove_foreign_key :orders, :users
42>>   end
43>> end
44
45=> :change
46
47>> RemoveUserForeignKeyFromOrders.new.change
48-- remove_foreign_key(:orders, :users)
49   (1.4ms)  SELECT sqlite_version(*)
50   (0.0ms)  begin transaction
51   (0.0ms)  PRAGMA foreign_keys
52   (0.0ms)  PRAGMA defer_foreign_keys
53   (0.0ms)  PRAGMA defer_foreign_keys = ON
54   (0.0ms)  PRAGMA foreign_keys = OFF
55   (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)
56   (0.3ms)  INSERT INTO "aorders" ("id","number","total","completed_at","created_at","updated_at","user_id")
57                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "orders"
58   (0.4ms)  DROP TABLE "orders"
59   (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)
60   (0.1ms)  INSERT INTO "orders" ("id","number","total","completed_at","created_at","updated_at","user_id")
61                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "aorders"
62   (0.1ms)  DROP TABLE "aorders"
63   (0.0ms)  PRAGMA defer_foreign_keys = 0
64   (0.0ms)  PRAGMA foreign_keys = 1
65   (0.7ms)  commit transaction
66   -> 0.0179s
67
68=> []
Copy

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

1>> class RemoveUserForeignKeyFromOrders < ActiveRecord::Migration[6.0]
2>>   def change
3>>     remove_foreign_key :orders, :users
4>>   end
5>> end
6
7=> :change
8
9>> RemoveUserForeignKeyFromOrders.new.change
10-- remove_foreign_key(:orders, :users)
11   (1.4ms)  SELECT sqlite_version(*)
12   (0.0ms)  begin transaction
13   (0.0ms)  PRAGMA foreign_keys
14   (0.0ms)  PRAGMA defer_foreign_keys
15   (0.0ms)  PRAGMA defer_foreign_keys = ON
16   (0.0ms)  PRAGMA foreign_keys = OFF
17   (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)
18   (0.3ms)  INSERT INTO "aorders" ("id","number","total","completed_at","created_at","updated_at","user_id")
19                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "orders"
20   (0.4ms)  DROP TABLE "orders"
21   (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)
22   (0.1ms)  INSERT INTO "orders" ("id","number","total","completed_at","created_at","updated_at","user_id")
23                     SELECT "id","number","total","completed_at","created_at","updated_at","user_id" FROM "aorders"
24   (0.1ms)  DROP TABLE "aorders"
25   (0.0ms)  PRAGMA defer_foreign_keys = 0
26   (0.0ms)  PRAGMA foreign_keys = 1
27   (0.7ms)  commit transaction
28   -> 0.0179s
29
30=> []
Copy

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.