Field-level validation is the most granular check. You look at one column at a time and ask: Is the data type correct? Is the length within limits? Is the format valid? Are NULL values handled properly? Think of it as checking each ingredient before cooking.
Wrong data type is a silent killer. Phone number stored as INT? Leading zeros vanish. Price stored as FLOAT? You get rounding errors. Date stored as VARCHAR? Sorting breaks. Always verify column types match the business requirement.
-- Step 1: Check column definitions
SELECT column_name, data_type, character_maximum_length,
numeric_precision, numeric_scale, is_nullable
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
-- What to look for:
-- phone should be VARCHAR(15), NOT INT
-- email should be VARCHAR(255)
-- balance should be DECIMAL(12,2), NOT FLOAT
-- created_at should be TIMESTAMP, NOT VARCHAR
-- is_active should be BOOLEAN, NOT VARCHAR(5)-- Find names that are suspiciously short (possible truncation)
SELECT user_id, full_name, LENGTH(full_name) AS name_length
FROM users
WHERE LENGTH(full_name) <= 2;
-- Find names hitting the column limit (truncation happened)
SELECT user_id, full_name, LENGTH(full_name) AS name_length
FROM users
WHERE LENGTH(full_name) = 50; -- If VARCHAR(50)
-- If you see many rows at exactly 50, data is being truncated
-- Validate email format (basic check)
SELECT user_id, email
FROM users
WHERE email NOT LIKE '%@%.%';
-- Catches: blank emails, missing @, missing domain
-- Validate phone number format
SELECT user_id, phone
FROM users
WHERE phone !~ '^[0-9]{10}$'; -- PostgreSQL regex
-- Catches: letters in phone, too short, too long-- Find NULL values in columns that should NEVER be NULL
SELECT 'users.full_name' AS field, COUNT(*) AS null_count
FROM users WHERE full_name IS NULL
UNION ALL
SELECT 'users.email', COUNT(*)
FROM users WHERE email IS NULL
UNION ALL
SELECT 'orders.total_amount', COUNT(*)
FROM orders WHERE total_amount IS NULL
UNION ALL
SELECT 'transactions.amount', COUNT(*)
FROM transactions WHERE amount IS NULL;
-- Verify NOT NULL constraints exist on critical columns
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'users'
AND column_name IN ('full_name', 'email', 'phone')
AND is_nullable = 'YES';
-- If any row returns, that column ALLOWS NULLs — potential bug| Field Check | SQL Pattern | What It Catches |
|---|---|---|
| Wrong data type | information_schema.columns query | Phone as INT, price as FLOAT, date as VARCHAR |
| Truncation | WHERE LENGTH(col) = MAX_LENGTH | Names cut off, addresses incomplete |
| Bad format | WHERE col NOT LIKE pattern | Emails without @, phone with letters |
| Unexpected NULLs | WHERE col IS NULL | Missing required data |
| Out of range | WHERE col < 0 OR col > MAX | Negative prices, age = 999, future birth dates |
Build a "field validation checklist" for each table. List every column, its expected type, length, format, and NULL rule. Run through it after every migration. Takes 10 minutes. Catches bugs worth days of debugging.
Key Point: Field-level validation checks each column individually for correct data type, length, format, and NULL handling.