validating-database-integrity

name	validating-database-integrity
description	Process use when you need to ensure database integrity through comprehensive data validation. This skill validates data types, ranges, formats, referential integrity, and business rules. Trigger with phrases like "validate database data", "implement data validation rules", "enforce data integrity constraints", or "validate data formats".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*)
version	1.28.0
author	Jeremy Longshore <[e-mail]>
license	MIT
tags	["database","validating-database"]
compatibility	Designed for Claude Code, also compatible with Codex and OpenClaw

Data Validation Engine

Overview

Implement and enforce data integrity rules at the database level using CHECK constraints, triggers, foreign keys, and custom validation functions across PostgreSQL and MySQL.

Prerequisites

• Database credentials with ALTER TABLE and CREATE FUNCTION permissions
• psql or mysql CLI for executing validation queries
• Current schema documentation or access to information_schema for column specifications
• Business rules document describing valid data ranges, formats, and relationships
• Backup of production data before applying new constraints (constraints may reject existing invalid data)

Instructions

1. Audit existing data quality by running validation queries before adding constraints. Check for NULL values in columns that should be required: SELECT column_name, COUNT(*) FILTER (WHERE column_name IS NULL) AS null_count, COUNT(*) AS total FROM table_name GROUP BY column_name.

2. Detect orphaned records (broken referential integrity): SELECT c.id FROM child_table c LEFT JOIN parent_table p ON c.parent_id = p.id WHERE p.id IS NULL. Document all orphaned records for cleanup or archival before adding foreign key constraints.

3. Validate data format compliance:

   • Email format: SELECT email FROM users WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
   • Phone format: SELECT phone FROM contacts WHERE phone !~ '^\+?[1-9]\d{6,14}$'
   • URL format: SELECT url FROM links WHERE url !~ '^https?://.+'
   • Date ranges: SELECT * FROM events WHERE start_date > end_date

4. Check numeric range violations: SELECT * FROM products WHERE price < 0 OR price > 999999.99 and SELECT * FROM users WHERE age < 0 OR age > 150. Map each column to its valid range based on business rules.

5. Identify duplicate records that violate intended uniqueness: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. Determine which duplicate to keep (most recent, most complete) and plan deduplication.

6. Generate CHECK constraints for validated rules:

   • ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price >= 0)
   • ALTER TABLE users ADD CONSTRAINT chk_email_format CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')