CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordsqlite
user2319683
user2319683

Reputation: 79

How to use ActiveRecord to force a foreign key in SQLite3?

SQLite3 does not allow making the columns of an existing table into a foreign keys but it does allow that when creating a new table, but this code does not seem to create a table with a foreign key when I open the .sqlite3 file?

class CreateProducts < ActiveRecord::Migration[5.0]
  def change
    create_table :products do |t|
      t.string :name
      t.text :description
      t.float :cost
      t.references :category, foreign_key: true

      t.timestamps
    end
  end
end

When I click "products" in DB Browser for SQLite, I expected it to display the following next to "products" in the schema:

CREATE TABLE `products` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name`  varchar,
    `description`   text,
    `cost`  float,
    `category_id`   integer,
    `created_at`    datetime NOT NULL,
    `updated_at`    datetime NOT NULL,
    FOREIGN KEY(`category_id`) REFERENCES categories ( id )
);

Instead, the schema had the following next to "products":

CREATE TABLE `products` (
    `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name`  varchar,
    `description`   text,
    `cost`  float,
    `category_id`   integer,
    `created_at`    datetime NOT NULL,
    `updated_at`    datetime NOT NULL,
);

So how can I make the migration force the .sqlite3 file to have have a foreign key?

Even after I manually edited the .sqlite3 file to include a foreign key between products and categories, the following in code in products_controller.rb returns false, false and true.

@current_connection = Product.connection
puts @current_connection.foreign_key_exists?(:categories, :products)
puts @current_connection.foreign_key_exists?(:products, :categories)
puts @current_connection.index_exists?(:products, :category_id)

If the .sqlite3 is being used, why is the output to falses and one true?

Also, will the PostgreSQL table have a foreign key when the migration to create products is run or will it have the same problem?

Upvotes: 2

Views: 720

Answers (1)

Manuel Costanzo
Manuel Costanzo

Reputation: 13

try this

class CreateProducts < ActiveRecord::Migration[5.0]
  def change
    create_table :products do |t|
      t.string :name
      t.text :description
      t.float :cost
      t.references :category, foreign_key: true
      add_foreign_key :products, :categories, foreign_key: true

      t.timestamps
    end
  end
end

result:

CREATE TABLE "products" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "description" text, "cost" float, "category_id" integer, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);
CREATE INDEX "index_products_on_category_id" ON "products" ("category_id");

Upvotes: 1

Related Questions