Use this prompt when a proposed schema change—such as a column drop, table rename, or primary key modification—could break existing foreign key relationships. The primary job is to produce a dependency impact report that identifies orphaned records, cascade risks, and constraint violations before the migration reaches production. The ideal user is a backend engineer, data engineer, or DBA who has the proposed DDL and access to current schema metadata, and who needs a structured risk assessment rather than a generic warning.

This prompt is not a replacement for running actual constraint checks against a staging database. It is a reasoning and planning tool that cross-references the proposed change against declared foreign key relationships. You must provide the current schema DDL, the proposed migration DDL, and optionally a sample of referencing table data as [CONTEXT]. The prompt works best when the harness supplies extracted foreign key metadata, row counts, and any known orphan counts before the model sees the input. Do not use this prompt for performance tuning, index strategy, or data type validation—those require separate specialized prompts.

Avoid using this prompt when the schema is not fully described in the input context or when foreign key relationships are enforced only at the application layer without database-level constraints. The model cannot inspect your live database; it reasons only from the metadata you provide. For high-risk migrations on tables containing PII, financial records, or healthcare data, always pair the prompt's output with a manual review step and a dry-run execution against a production-like environment. The next section provides the copy-ready prompt template you can adapt and wire into your migration review pipeline.

This prompt template is designed to be the core instruction set you send to an LLM. It takes a proposed schema change and a snapshot of the current database metadata to produce a structured dependency impact report. The report focuses on orphaned records, cascade risks, and constraint violations. The template uses square-bracket placeholders for all dynamic inputs, making it straightforward to integrate into a CI/CD pipeline, a migration review tool, or a manual pre-flight checklist.

textCopy
You are a senior database reliability engineer. Your task is to analyze a proposed schema change against the current database metadata and produce a Foreign Key Dependency Impact Report.

# INPUTS
- **Proposed Change (DDL):**
  ```sql
  [PROPOSED_DDL_STATEMENT]

• Current Schema Metadata (JSON):

  jsonCopy
  [CURRENT_SCHEMA_METADATA]

  The metadata includes table names, columns, data types, and existing foreign key constraints with their delete/update rules.
• Row Counts (JSON):

  jsonCopy
  [TABLE_ROW_COUNTS]

  Approximate row counts for all tables referenced in the metadata.

OUTPUT SCHEMA

You must respond with a single JSON object conforming to this structure: { "analysis_id": "string", "summary": "A one-sentence executive summary of the overall risk.", "findings": [ { "severity": "CRITICAL | HIGH | MEDIUM | LOW", "type": "ORPHANED_RECORDS | CASCADE_RISK | CONSTRAINT_VIOLATION | MISSING_INDEX", "description": "A clear, specific description of the finding.", "affected_objects": ["schema.table.column"], "estimated_impact_rows": "integer or null", "remediation": "A concrete, actionable step to resolve the issue before deployment." } ], "rollback_safety": "SAFE | RISKY | DESTRUCTIVE", "requires_manual_review": true or false }

CONSTRAINTS

1. Ground every finding in the provided metadata. Do not speculate about tables or constraints not present in the input.
2. If the proposed change adds a foreign key, check that the data types and collations match exactly. Flag any mismatch as a CONSTRAINT_VIOLATION.
3. If the proposed change drops a column, check if it is part of any existing foreign key. If so, flag it as a CRITICAL finding.
4. For CASCADE_RISK findings, estimate the blast radius by using the provided row counts of child tables.
5. If no issues are found, return an empty findings array and a summary stating the change is safe.
6. Set requires_manual_review to true if any finding has a severity of CRITICAL or HIGH.

To adapt this template, replace the placeholders with data from your own systems. The [PROPOSED_DDL_STATEMENT] should be the exact SQL from the pull request or migration file. The [CURRENT_SCHEMA_METADATA] should be a JSON export from your database's information schema, including all relevant tables, columns, and constraints. The [TABLE_ROW_COUNTS] input is critical for the model to estimate the blast radius of a CASCADE operation; without it, the risk assessment is incomplete. For high-risk environments, always set requires_manual_review to true in your application logic if the model fails to do so, and pipe any CRITICAL finding directly to a human approval queue before the migration can proceed.

The Foreign Key Constraint Validation Prompt is not a standalone tool—it is a reasoning step inside a larger harness that must supply actual schema metadata and proposed DDL changes. The prompt alone cannot query your database or parse your migration files. To make it useful, you need a harness that extracts the current foreign key graph, identifies the tables and columns affected by the proposed change, and packages both into the prompt's [CURRENT_SCHEMA] and [PROPOSED_CHANGES] placeholders. Without this cross-reference, the model will produce plausible-sounding but ungrounded analysis.

Build the harness in three stages: extract, assemble, and validate. In the extract stage, query your database's information schema (e.g., INFORMATION_SCHEMA.KEY_COLUMN_USAGE in MySQL/PostgreSQL or sys.foreign_keys in SQL Server) to produce a structured representation of every existing foreign key—source table, source column, target table, target column, and delete/update rule. In parallel, parse the proposed migration script to identify ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statements, DROP FOREIGN KEY operations, and any column modifications that could break existing references. The assemble stage merges these into the prompt template, adding a [CONSTRAINTS] block that specifies the output must include orphaned row estimates, cascade path analysis, and a severity classification for each finding. The validate stage runs after the model responds, checking that every referenced table and column actually exists in the supplied schema metadata—this catches hallucinated constraints.

For production use, implement a retry loop with escalating context. If the model's output fails validation (e.g., it references a table not in the input schema), re-invoke the prompt with the validation error appended as a [PREVIOUS_ERRORS] block and instruct the model to correct the mistake. Limit retries to two attempts; after that, flag the finding for human review. Log every invocation—input schema hash, proposed change diff, model response, validation results, and retry count—so you can trace false positives and refine the prompt over time. Model choice matters: use a model with strong structured reasoning (GPT-4o, Claude 3.5 Sonnet, or equivalent) because weaker models will miss transitive cascade paths and produce vague severity classifications.

The highest-risk output from this prompt is a false negative—a missed cascade risk or orphaned record scenario that reaches production. Mitigate this by never treating the prompt's output as the final word. Always run the generated dependency impact report through a human review gate for migrations that touch tables with more than a configurable row-count threshold or that modify delete rules. Pair the prompt with a dry-run harness that executes the proposed DDL in a staging environment and runs the model's suggested validation queries (e.g., SELECT COUNT(*) FROM child_table WHERE parent_id NOT IN (SELECT id FROM parent_table)) to confirm orphan estimates. The prompt is a reasoning accelerator, not a replacement for actual constraint enforcement.