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.