gregnavis / active_record_doctor

Identify database issues before they hit production.
MIT License
1.72k stars 56 forks source link

unindexed_foreign_keys: False positive for custom primary keys #156

Closed jdufresne closed 9 months ago

jdufresne commented 9 months ago

My application uses a form of multi-table inheritance such that child table's primary key is a foreign key on the parent table.

Given the tables:

create_table "parents" do |t|
  t.string "name", null: false
end

create_table "childs", primary_key: "parent_id", id: :bigint, default: nil do |t|
  t.string "name", null: false
end

add_foreign_key "childs", "parents", column: "parent_id"

This will generate a table that looks like:

\d childs
                     Table "public.childs"
  Column   |       Type        | Collation | Nullable | Default
-----------+-------------------+-----------+----------+---------
 parent_id | bigint            |           | not null |
 name      | character varying |           | not null |
Indexes:
    "childs_pkey" PRIMARY KEY, btree (parent_id)
Foreign-key constraints:
    "fk_rails_7fda5a7c59" FOREIGN KEY (parent_id) REFERENCES parents(id)

As you can see, the column parent_id is a foreign key, has a btree index, and is also the primary key.

When I run active_record_doctor, this gets reported as missing an index:

$ bundle exec rails active_record_doctor:unindexed_foreign_keys
add an index on childs(parent_id) - foreign keys are often used in database lookups and should be indexed for performance reasons

Expected: No error.

fatkodima commented 9 months ago

Will be fixed by #157.