Before you test if data flows correctly, you need to verify that the tables are built correctly. Wrong data type? Data gets truncated. Missing constraint? Invalid data sneaks in. No index? Queries take forever. Structural testing catches all of this.
Does the table exist? Are all expected columns present? Are the data types correct? This is your first check after any migration or deployment.
-- MySQL: Check table structure
DESCRIBE users;
-- PostgreSQL: Check table structure
SELECT column_name, data_type, character_maximum_length,
is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;Constraints are the rules that protect your data. If they are missing or wrong, garbage data enters the system. Here are the five constraints every tester must verify:
| Constraint | What It Does | What Happens If Missing | How to Test |
|---|---|---|---|
| PRIMARY KEY | Unique identifier for each row | Duplicate rows, no reliable way to find a record | Try inserting a row with duplicate PK — should fail |
| FOREIGN KEY | Links one table to another | Orphan records, broken relationships | Try inserting a child record with non-existent parent ID — should fail |
| NOT NULL | Column must have a value | Blank names, empty emails, missing data | Try inserting a row with NULL in the column — should fail |
| UNIQUE | No duplicate values in column | Same email registered twice, duplicate accounts | Try inserting two rows with same value — second should fail |
| CHECK | Value must meet a condition | Negative prices, age = 500, salary = -1 | Try inserting a value that violates the condition — should fail |
-- Test NOT NULL: This should FAIL
INSERT INTO users (user_id, full_name, email)
VALUES (101, NULL, 'test@email.com');
-- Expected: ERROR — Column 'full_name' cannot be null
-- Test UNIQUE: Insert duplicate email — should FAIL
INSERT INTO users (user_id, full_name, email)
VALUES (102, 'Test User', 'ravi.sharma@email.com');
-- Expected: ERROR — Duplicate entry for key 'email'
-- Test FOREIGN KEY: Insert order for non-existent user — should FAIL
INSERT INTO orders (order_id, user_id, total_amount)
VALUES (5001, 99999, 500.00);
-- Expected: ERROR — Foreign key constraint fails
-- Test CHECK: Insert negative price — should FAIL
INSERT INTO products (product_id, name, price)
VALUES (301, 'Widget', -50.00);
-- Expected: ERROR — Check constraint violatedIndexes make queries fast. Without them, the database scans every single row. For a table with 10 million rows, that is the difference between 0.01 seconds and 45 seconds.
-- MySQL: List all indexes on a table
SHOW INDEX FROM orders;
-- PostgreSQL: List all indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';Never skip structural testing after a database migration. I have seen deployments where a developer forgot to add a NOT NULL constraint on the email column. Result: 2,000 users registered with blank emails in one weekend. All because nobody verified the schema after migration.
Q: How do you verify database constraints during testing?
A: I verify constraints by writing negative test cases — deliberately trying to violate each constraint. For NOT NULL, I try inserting NULL values. For UNIQUE, I insert duplicate values. For FOREIGN KEY, I insert a child record with a non-existent parent ID. For CHECK, I insert values that violate the condition. If any of these succeed instead of throwing an error, the constraint is missing or broken. I also query information_schema to confirm constraints exist in the schema definition.
Key Point: Structural testing verifies the database blueprint — tables, columns, data types, constraints, and indexes.