---
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 <jeremy@intentsolutions.io>
license: MIT
tags:
  - database
  - validating-database
compatibility: Designed for Claude Code
title: validating-database-integrity
canonical_url: https://skilld.dev/gh/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity
last_updated: 2026-10-07T06:06:28.000Z
---

> Index of every page on this site: [llms.txt](https://skilld.dev/llms.txt)

> **Skill from skilld.dev.** Follow the user's requested action.
> If the user asked to fork this Skill, follow the fork workflow below. Do not execute the Skill instructions while copying it.
> Otherwise, follow the instructions below for this session. You do not need to install anything.
>
> Supporting files, fetch one when the Skill refers to it: [assets/README.md](https://skilld.dev/api/skills-raw/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity/assets/README.md), [references/README.md](https://skilld.dev/api/skills-raw/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity/references/README.md), [scripts/configure_validation_rules.py](https://skilld.dev/api/skills-raw/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity/scripts/configure_validation_rules.py), [scripts/generate_validation_report.py](https://skilld.dev/api/skills-raw/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity/scripts/generate_validation_report.py), [scripts/README.md](https://skilld.dev/api/skills-raw/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity/scripts/README.md).
>
> If the user asked to install this Skill, run `npx skilld install jeremylongshore/tons-of-skills-marketplace/validating-database-integrity`. Install writes the Skill files into the project, so every session loads them.
>
> ## Fork workflow
>
> A fork creates an editable local Skill with its original author and licence. The request authorizes copying and local installation.
> 1. Check `./skills/validating-database-integrity`, the project lockfile, and selected Agent targets together. If the local directory or installed Skill exists, stop. Never overwrite an existing directory or Agent target.
> 2. Read [source metadata](https://skilld.dev/api/v1/skills/jeremylongshore/tons-of-skills-marketplace/validating-database-integrity) once. Use sourceUrl, sourceCommit, skillPath, sourceGone, and license. If the source is gone or its path is missing, stop. If license is null, read licence files at the source commit.
> 3. Fetch only the source commit into a temporary Git repository. Do not clone full history. Derive repository_url from sourceUrl, including repository renames. If sourceCommit is absent, resolve the sourceUrl ref once. Set source_commit to that actual commit. Run these commands in one shell call:
>
> ```sh
> git init --quiet "$temporary_dir"
> git -C "$temporary_dir" fetch --quiet --depth=1 "$repository_url" "$source_commit"
> git -C "$temporary_dir" checkout --quiet --detach FETCH_HEAD
> ```
>
> Read applicable licence declarations and notices at that commit. If copying is not permitted, report the restriction and stop.
> 4. Inspect source entries together, then copy the directory containing skillPath into `./skills/validating-database-integrity`. Use the user's path if selected. Keep the original SKILL.md, relative links, scripts, binary assets, and executable modes. Exclude .git metadata. Reject symlinks and paths outside the Skill directory. After checking entries, use cp -a where available. A regular source directory needs no custom copy script. Do not save this page wrapper as SKILL.md.
> Preserve author credit, notices, and applicable licence files from repository or parent directories. Add PROVENANCE.md with the Skill page, source URL, actual commit, original path, and licence. Retain any existing PROVENANCE.md and record new provenance separately. Batch source inspection, copying, and provenance work where practical.
> 5. In the project root, run `skilld install ./skills/validating-database-integrity --mode copy --plain`. If skilld is unavailable, use `npx skilld install ./skills/validating-database-integrity --mode copy --plain`. This known command needs no help lookup. Install does not support --json. Use detected Agent targets, or add --agent for the targets the user selected. Install the local path, never the upstream selector. If installation fails, preserve the local copy and report the exact failure.
> 6. Confirm the local lockfile source and installed Agent copies once. Report the local path, actual commit, and Agent targets. After edits, reinstall the same local path. Upstream updates must not replace it. Do not publish or push unless the user asks.

# 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,}$')`
   - `ALTER TABLE events ADD CONSTRAINT chk_date_order CHECK (start_date <= end_date)`
   - `ALTER TABLE orders ADD CONSTRAINT chk_status_valid CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))`

7. Create foreign key constraints with appropriate cascade behavior:
   - `ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT`
   - Use `ON DELETE CASCADE` for dependent data (order_items when order is deleted)
   - Use `ON DELETE SET NULL` for optional relationships (assigned_to when user is deactivated)

8. Implement complex business rule validation using database triggers when CHECK constraints are insufficient:
   - Trigger that prevents order total from exceeding customer credit limit
   - Trigger that enforces at least one admin user per organization
   - Trigger that validates JSON schema for JSONB columns

9. Apply constraints in a safe two-phase approach:
   - Phase 1: Run validation queries to find all violations. Generate data cleanup scripts. Execute cleanup.
   - Phase 2: Apply constraints with `NOT VALID` option (PostgreSQL): `ALTER TABLE users ADD CONSTRAINT chk_email CHECK (email ~ '...') NOT VALID` then `ALTER TABLE users VALIDATE CONSTRAINT chk_email` (validates existing data without blocking writes).

10. Generate a data quality report summarizing: total records per table, violation counts by constraint type, cleanup actions taken, constraints applied, and remaining data quality issues requiring manual review.

## Output

- **Data quality audit report** with violation counts, examples, and severity ratings
- **Data cleanup scripts** (SQL) to fix violations before constraint application
- **Constraint DDL scripts** with CHECK, FOREIGN KEY, NOT NULL, and UNIQUE constraints
- **Validation triggers** for complex business rules beyond simple constraints
- **Ongoing validation queries** for periodic data quality monitoring

## Error Handling

| Error | Cause | Solution |
|-------|-------|---------|
| `check constraint violated by existing row` | Existing data fails the new constraint | Run the validation query first to find violations; clean up data; use `NOT VALID` option to add constraint without checking existing data, then validate separately |
| `cannot add foreign key: referenced row not found` | Orphaned child records reference non-existent parent | Clean up orphaned records first with DELETE or UPDATE to valid parent; or insert missing parent records |
| `column cannot be made NOT NULL: contains NULL values` | Existing rows have NULL in the target column | Backfill NULLs with `UPDATE table SET column = default_value WHERE column IS NULL` before adding NOT NULL |
| Trigger function causes performance regression | Complex validation logic executes on every INSERT/UPDATE | Optimize trigger function; use WHEN clause to limit trigger firing; consider CHECK constraints instead of triggers for simple rules |
| Circular foreign key prevents constraint creation | Tables reference each other, preventing creation order | Use `ALTER TABLE ADD CONSTRAINT` after both tables exist; or use `DEFERRABLE INITIALLY DEFERRED` constraints |

## Examples

**Auditing a legacy database with 50,000 invalid email addresses**: Validation query reveals 50,000 of 2M user records have invalid email formats (missing @, double dots, spaces). A cleanup script normalizes common issues (trim whitespace, lowercase) and flags 3,000 unfixable records for manual review. After cleanup, a CHECK constraint with regex validation is applied.

**Enforcing referential integrity on a database without foreign keys**: An application relied on application-level FK enforcement, resulting in 12,000 orphaned order_items, 800 orphaned payments, and 200 orphaned reviews. Cleanup scripts archive orphaned records to backup tables, then foreign key constraints with `ON DELETE CASCADE` are added. A nightly validation job monitors for new orphans.

**Implementing business rules for a financial application**: Constraints enforce: account balance cannot be negative (`CHECK (balance >= 0)`), transfer amount must be positive (`CHECK (amount > 0)`), transaction date cannot be in the future (`CHECK (transaction_date <= CURRENT_DATE)`), and a trigger prevents transfers between accounts owned by different customers unless explicitly authorized.

## Resources

- PostgreSQL CHECK constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
- PostgreSQL triggers: https://www.postgresql.org/docs/current/triggers.html
- MySQL CHECK constraints (8.0.16+): https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html
- Data validation patterns: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS
- NOT VALID constraint option: https://www.postgresql.org/docs/current/sql-altertable.html
