SQL Constraint Edge Cases

[SumitPatel-HQ]

SQL Edge Case Handling System (Production-Ready Guide)

Overview

This document defines a structured approach to handling SQL execution edge cases in an AI-driven system. The goal is to transform raw database errors into actionable, user-friendly, and context-aware responses.

Instead of reacting to individual errors, this system uses:

• Error classification

• Intent detection

• Guided remediation

---

1. Core Architecture

1.1 Error Normalization Layer

Convert raw DB errors into structured categories.

Example Mapping

{
  "1701": "FK_TRUNCATE_BLOCK",
  "1213": "DEADLOCK",
  "1205": "LOCK_TIMEOUT",
  "1062": "DUPLICATE_ENTRY",
  "1048": "NOT_NULL_VIOLATION",
  "1451": "FK_DELETE_BLOCK",
  "1452": "FK_INSERT_FAIL",
  "1142": "PERMISSION_DENIED"
}

---

1.2 Intent vs Execution Gap Detection

User Intent | Failure Type | System Response -- | -- | -- Clear table | FK constraint block | Suggest delete order Insert data | Duplicate key | Suggest upsert Modify data | Read-only restriction | Explain limitation Delete record | FK dependency | Suggest cascading strategy

---

1.3 Response Structure

Every error response should contain:

{
  "error_type": "FK_TRUNCATE_BLOCK",
  "message": "Cannot truncate table due to foreign key constraints.",
  "cause": "Table is referenced by child tables.",
  "solutions": [
    "Use DELETE instead of TRUNCATE",
    "Delete dependent rows first",
    "Temporarily disable foreign key checks (advanced)"
  ]
}

---

2. Edge Case Categories

---

2.1 Constraint Violations

Foreign Key Violations

Case 1: Delete Blocked

DELETE FROM users WHERE id = 1;

Error: Cannot delete parent row

Solution:

• Delete child rows first

• Use ON DELETE CASCADE

---

Case 2: Insert Fails

INSERT INTO orders(user_id) VALUES(999);

Error: Parent row does not exist

Solution:

• Insert parent row first

• Validate foreign key before insert

---

TRUNCATE with FK (MySQL 1701)

Problem:

• TRUNCATE fails if table is referenced

Solution:

DELETE FROM child_table;
DELETE FROM parent_table;

---

Unique Constraint Violation

INSERT INTO users(email) VALUES('test@gmail.com');

Solution:

• Use INSERT IGNORE

• Use ON DUPLICATE KEY UPDATE

---

NOT NULL Violation

INSERT INTO users(name) VALUES(NULL);

Solution:

• Provide required field

• Add default value

---

2.2 Execution Restrictions

Read-Only Mode

Error:

• Only SELECT/CTE allowed

Solution:

• Inform user mutation is not allowed

• Suggest SELECT alternative

---

Role-Based Restrictions

SELECT * FROM secure_table;

Error: Permission denied

Solution:

• Request access

• Use authorized tables

---

2.3 Transaction & Locking Issues

Deadlock (1213)

Solution:

• Retry transaction

• Ensure consistent query order

---

Lock Timeout (1205)

Solution:

• Reduce transaction scope

• Optimize query

• Add indexes

---

2.4 Data Type Errors

Invalid Data Type

INSERT INTO users(age) VALUES('abc');

Solution:

• Validate input type

• Use correct datatype

---

Silent Data Truncation

Risk:

• Data loss without failure

Solution:

• Enable strict mode

• Validate input length

---

2.5 Schema Errors

Column Not Found

SELECT username FROM users;

Solution:

• Suggest closest column name

• Introspect schema

---

Table Not Found

Solution:

• Check database context

• List available tables

---

2.6 Query Logic Errors

GROUP BY Issue (ONLY_FULL_GROUP_BY)

SELECT name, COUNT(*) FROM users;

Solution:

• Add GROUP BY

• Adjust SQL mode

---

2.7 Dangerous Queries

DELETE without WHERE

DELETE FROM users;

System Action:

• Block or warn

• Ask confirmation

---

DROP / TRUNCATE

System Action:

• Require confirmation

• Explain irreversible impact

---

2.8 Performance Issues

Full Table Scan

Solution:

• Suggest indexes

• Rewrite query

---

N+1 Queries

Solution:

• Batch queries

• Use joins

---

2.9 Engine-Specific Cases

MySQL

• TRUNCATE + FK restriction

• Implicit commits

• Engine differences

PostgreSQL

• Strict typing

• Different cascade handling

---

3. Implementation Blueprint

---

3.1 Error Classifier (Python Example)

def classify_error(error_code):
    mapping = {
        1701: "FK_TRUNCATE_BLOCK",
        1213: "DEADLOCK",
        1062: "DUPLICATE_ENTRY",
        1451: "FK_DELETE_BLOCK"
    }
    return mapping.get(error_code, "UNKNOWN_ERROR")

---

3.2 Response Generator

def generate_response(error_type):
    responses = {
        "FK_TRUNCATE_BLOCK": {
            "message": "Cannot truncate due to foreign key constraints.",
            "solutions": [
                "Use DELETE instead",
                "Delete child records first"
            ]
        }
    }
    return responses.get(error_type, {})

---

3.3 Middleware Integration (FastAPI)

try:
    execute_query(query)
except Exception as e:
    error_code = extract_code(e)
    error_type = classify_error(error_code)
    response = generate_response(error_type)
    return JSONResponse(status_code=400, content=response)

---

4. Advanced Enhancements

---

4.1 Query Rewriting Engine

Example:

Input:

TRUNCATE city;

Output Suggestion:

DELETE FROM orders WHERE city_id IN (SELECT id FROM city);
DELETE FROM city;

---

4.2 AI Agent Prompt Strategy

Your agent should:

• Detect intent

• Detect failure reason

• Suggest corrected query

---

4.3 Safety Layer

Block:

• DELETE without WHERE

• DROP DATABASE

• Mass updates

---

5. Testing Strategy

---

Add Regression Tests for:

• FK truncate failure

• Duplicate insert

• Deadlock simulation

• Permission denied

• Read-only mode

---

Example (pytest)

def test_fk_truncate_block():
    response = client.post("/query", json={"query": "TRUNCATE city"})
    assert response.status_code == 400
    assert "foreign key" in response.json()["message"]

---

6. Final Principle

You are not building a query executor.

You are building a:

SQL Reasoning & Recovery System

Key capabilities:

• Understand intent

• Interpret database errors

• Provide actionable fixes

• Prevent dangerous operations

---

7. Next Steps

• Expand error mapping coverage

• Add query rewriting layer

• Integrate schema introspection

• Add AI-assisted correction

---

End of Document