Warn about unindexed foreign key constraints.

Open
#334 1 comment 3 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Feature
Clarity
Needs clarification
Activity status
Stale
Tech stack
postgresql, rust, sql
Domain
databases, tooling

Research direction

Start by reviewing the proposed PostgreSQL query and determine how it would integrate with the linter's running-database analysis. The issue provides no file or test entry point; done would mean warning about foreign key constraints without a suitable index while avoiding false positives.

Written by the indexing model from the issue text.

Description

enhancement

I'd like to have is a warning that a foreign key constraint isn't indexed. Static analysis of the migration file can't provide enough information to eliminate false positives for such a rule, so querying a running database would be required.

Such a query might look like this:

WITH indexes AS (
SELECT
    n.nspname as schema_name,
    t.relname as table_name,
    a.attname as column_name
FROM
    pg_class t
JOIN
    pg_index i ON t.oid = i.indrelid
JOIN
    pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
JOIN
    pg_namespace n ON t.relnamespace = n.oid
WHERE
    t.relkind = 'r' -- real tables
    AND array_position(i.indkey, a.attnum) = 0 -- only first column of possibly compound index
),
foreign_keys AS (
SELECT
    n.nspname AS schema_name,
    cl.relname AS table_name,
    a.attname AS column_name,
    ct.conname AS constraint_name
FROM
    pg_constraint ct
    JOIN pg_class cl ON ct.conrelid = cl.oid
    JOIN pg_namespace n ON cl.relnamespace = n.oid
    JOIN pg_attribute a ON a.attnum = ANY(ct.conkey) AND a.attrelid = cl.oid
WHERE
    ct.contype = 'f'
)
SELECT
    schema_name as "schema!",
    table_name as "table!",
    column_name as "name!"
FROM foreign_keys
LEFT JOIN indexes USING (schema_name, table_name, column_name)
WHERE indexes.column_name IS NULL
AND schema_name = ANY($1)

(query is not perfect but you get the idea)

Dominant language
Rust
Stars
1.2k
Forks
70
Avg merge
52m
Merged PRs (30d)
47

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from sbdchd/squawk

All issues in sbdchd/squawk

Similar issues

More Rust issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.