---
title: "Rails 6 add_foreign_key & remove_foreign_key SQLite3"
description: "Rails 6 adds add_foreign_key and remove_foreign_key for SQLite3"
canonical_url: "https://www.bigbinary.com/blog/rails-6-adds-add_foreign_key-and-remove_foreign_key-for-sqlite3"
markdown_url: "https://www.bigbinary.com/blog/rails-6-adds-add_foreign_key-and-remove_foreign_key-for-sqlite3.md"
---

# Rails 6 add_foreign_key & remove_foreign_key SQLite3

Rails 6 adds add_foreign_key and remove_foreign_key for SQLite3

- Author: Amit Choudhary
- Published: September 24, 2019
- Categories: Rails 6, Rails

Rails provides
[add_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key)
to add foreign key constraint for a column on a table.

It also provides
[remove_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-remove_foreign_key)
to remove the foreign key constraint.

Before Rails 6,
[add_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key)
and
[remove_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-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](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key)
and
[remove_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-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](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key)
and then try removing it using
[remove_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-remove_foreign_key).

```ruby
>> 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](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key)
and
[remove_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-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](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key).

```ruby
>> 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](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-remove_foreign_key).

```ruby
>> 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](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key)
and
[remove_foreign_key](https://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-remove_foreign_key)
work and were able to add and remove foreign key constraint respectively.

Here is the relevant [pull request](https://github.com/rails/rails/pull/35212).

## Links

- [Human page](https://www.bigbinary.com/blog/rails-6-adds-add_foreign_key-and-remove_foreign_key-for-sqlite3)