PostgreSQL stops the statement with ERROR: index "idx_users_email" does not exist when the index is already gone, and in a migration that error aborts the whole transaction. Nothing was lost: no data is dropped by a failed DROP INDEX. Rerun the migration with DROP INDEX IF EXISTS, which turns the error into a notice.
Why it happens
A migration that drops an index assumes the index exists. It does not when the migration already ran once on that database, when a previous step dropped it, or when environments drifted (the index was only created by hand on staging). Without IF EXISTS the statement is not idempotent, so a retry fails.
How to fix it
On PostgreSQL, write DROP INDEX IF EXISTS. On a large, busy table use DROP INDEX CONCURRENTLY IF EXISTS, which does not take a lock that blocks writes, and run it outside a transaction block (PostgreSQL rejects CONCURRENTLY inside one).
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;MySQL 8.0 has no IF EXISTS for DROP INDEX (MariaDB does); there, check information_schema.statistics before dropping.
How to stop it from happening again
VERICTO-013 · DROP INDEX without IF EXISTS · Severity: High · AST node: DropStmt > ObjectType = INDEX (no IF EXISTS)
The statement the rule reports
-- migrations/0044_drop_index.sql
DROP INDEX idx_users_email;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0044_drop_index.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
! migrations/0044_drop_index.sql FLAGGED VERICTO-013 high
DropStmt > ObjectType = INDEX (no IF EXISTS)
fix: DROP INDEX IF EXISTS <index_name>
0 blocked · 0 allowed · 1 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
In a migration file the CLI marks it FLAGGED and exits with 0: a schema change in a migration is usually intended, so it is raised for review instead of blocked. With --fail-on flag (or fail_on = "flag" in .vericto.toml) it exits with 1 and the job fails. Outside a migration (--sql, stdin or another file) it blocks, as the proxy and MCP check_sql do.
The corrected version
-- migrations/0044_drop_index.sql
DROP INDEX IF EXISTS idx_users_email;
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0044_drop_index.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
0 blocked · 1 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
Try it free, no account
The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).
npx @vericto/vericto-cli@2.3.0 check --offline migrations/
On every pull request
The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:
on: pull_request
jobs:
vericto:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
with: { fetch-depth: 0 }
- uses: vericto/check-sql@v1
with: { offline: true, upload-sarif: false }
At runtime, for AI agents
The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.
The AST condition in the engine
The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:
"VERICTO-013" => find(stmts, |s| {
s.kind == StatementKind::Drop
&& matches!(s.drop_object, Some(DropObjectKind::Index))
&& !s.drop_index_if_exists
})
.map(|s| {
let mut v = violation(rule, s, Some("DROP INDEX IF EXISTS <index_name>".to_string()));
v.ast_node_path = "DropStmt > ObjectType = INDEX (no IF EXISTS)".to_string();
v
}),
Frequently asked questions
Related rules
- VERICTO-010: DROP TABLE or DROP DATABASE
- VERICTO-015: ALTER TABLE DROP COLUMN
- VERICTO-012: DROP SCHEMA