ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey; removes the rule that every orders.user_id points to an existing user. No row is deleted, but from that moment the database accepts orders for users that do not exist. Add the constraint back as soon as you can; if rows that break it were written in the meantime, clean them first.
How to recover the data
- Find the rows that break the rule:
SELECT o.id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE o.user_id IS NOT NULL AND u.id IS NULL; - Fix or remove those rows, depending on what the application expects.
- On PostgreSQL, add the constraint back without a long lock:
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;thenALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;
Why it happens
Constraints get dropped to make a bulk load, a data fix or a migration "go through" when it hits a violation, and the step that adds them back is forgotten or fails. Dropping a primary key or unique constraint also drops the index behind it, so queries that relied on it slow down.
How to fix it
Treat a constraint as part of the schema: drop it only in a reviewed, versioned migration that says why, and recreate it in the same change if the drop is temporary. When a load hits violations, fix the data rather than the constraint. To change a constraint, add the new one first (with NOT VALID on PostgreSQL, then VALIDATE) and drop the old one after.
How to stop it from happening again
VERICTO-017 · ALTER TABLE DROP CONSTRAINT (FK/PK/CHECK) · Severity: High · AST node: AlterTableStmt > DropConstraint
The statement the rule reports
-- migrations/0047_drop_fk.sql
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0047_drop_fk.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/0047_drop_fk.sql FLAGGED VERICTO-017 high
AlterTableStmt > DropConstraint
fix: Drop constraints only via a reviewed, versioned migration
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.
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-017" => find(stmts, |s| {
s.kind == StatementKind::AlterTable
&& matches!(s.alter_table_kind, Some(AlterTableKind::DropConstraint))
})
.map(|s| {
let mut v = violation(
rule,
s,
Some("Drop constraints only via a reviewed, versioned migration".to_string()),
);
v.ast_node_path = "AlterTableStmt > DropConstraint".to_string();
v
}),
Frequently asked questions
Related rules
- VERICTO-016: ALTER TABLE RENAME (table or column)
- VERICTO-018: ALTER TABLE ALTER COLUMN TYPE
- VERICTO-019: ALTER TABLE DISABLE TRIGGER / RLS