To test that a database column rejects NULL, attempt an insert and an update that set a required column to SQL NULL, and assert that each write fails. For an optional column, set it to NULL on insert and update, and assert that both writes succeed. Run these tests against the database engine and version used in production: NOT NULL rejects SQL NULL, not an empty string.
Build a small test table
Use an isolated test database and the target engine’s native schema syntax. This example defines one required text column and one nullable text column:
As an Amazon Associate I earn from qualifying purchases.
CREATE TABLE field_test (
id INTEGER PRIMARY KEY,
required_value TEXT NOT NULL,
optional_value TEXT
);The primary key identifies rows for update tests. The separate required_value column lets the test check nullability independently of primary-key behavior; PostgreSQL primary keys are already non-null.
Exercise inserts and updates
Test the operations separately. A valid write to the required column should succeed, an explicit NULL for it should fail, and a nullable column should accept NULL.
#1 Best Overall
-- Required value supplied: should succeed.
INSERT INTO field_test (id, required_value, optional_value)
VALUES (1, 'present', NULL);
-- Required value explicitly NULL: should fail.
INSERT INTO field_test (id, required_value, optional_value)
VALUES (2, NULL, 'optional');
-- Optional value NULL: should succeed.
INSERT INTO field_test (id, required_value, optional_value)
VALUES (3, 'present', NULL);
-- Updating a required value to NULL: should fail.
UPDATE field_test SET required_value = NULL WHERE id = 1;
-- Updating an optional value to NULL: should succeed.
UPDATE field_test SET optional_value = NULL WHERE id = 1;In an automated suite, wrap each statement with an assertion for its expected outcome: success for valid required values and nullable-column writes, and a constraint violation for attempts to null the required column. SQLite documents constraint checking for both INSERT and UPDATE; do not treat a successful insert test as proof that updates are covered.
Keep expected failures isolated so one rejected statement does not hide subsequent cases. If a failed write occurs inside a transaction, follow your database driver’s rules for rolling back or recovering the transaction before continuing.
Test matrix
| Field policy | Insert test | Update test | Expected result |
|---|---|---|---|
Required (NOT NULL) |
Insert a valid value, then explicitly try NULL |
Set the value to NULL |
Valid write succeeds; writes with NULL fail |
| Optional (nullable) | Set the value to NULL |
Set the value to NULL |
Both succeed, assuming no other constraint, trigger, or rule rejects them |
| Text with a blank-value policy | Set the value to '' |
Set the value to '' |
Check the separate application or schema policy; NOT NULL alone does not mean non-empty |
Keep NULL, empty strings, and omitted columns distinct
SQL NULL is not an empty string
NULL represents a missing or unknown value in SQL; '' is a zero-length string. MySQL’s Reference Manual makes the distinction explicit: “Both statements insert a value into the phone column, but the first inserts a NULL value and the second inserts an empty string.” Therefore, if your application treats blank text as missing, test that behavior separately rather than relying on NOT NULL. To find nulls in MySQL, use IS NULL, not = NULL. MySQL Reference Manual: Problems with NULL Values.
Recommended Free Tools
Omitted columns need their own case when relevant
An explicit NULL test does not cover an insert that leaves the column out. Add an omitted-column test if the application uses that write path. Its outcome can depend on the column’s default and the engine’s SQL mode, so record the schema and configuration used by the test. Explicitly supplying NULL remains the clearest test of null rejection.
Rank #3
Do not substitute CHECK for NOT NULL
A check expression can evaluate to NULL, and in PostgreSQL a CHECK constraint is satisfied when its expression is true or null. Consequently, CHECK (value <> '') alone does not ensure that value is non-null. Use NOT NULL for the nullability rule, then test any blank-string rule separately. PostgreSQL 16: Constraints.
Match the test to the production engine and version
Constraint behavior and schema-change options are engine- and version-specific. Run these tests using the same database engine and version as production, and use its native schema syntax. SQLite documents enforcement during ordinary inserts and updates; its integrity-checking facilities for corruption scenarios are a different concern from testing normal writes. SQLite: CREATE TABLE.
If the purpose is to validate a required-field constraint in PostgreSQL, test a non-key column: a primary key already carries non-null behavior. PostgreSQL 18: Constraints.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Migration tests also need version awareness. SQLite 3.53.0, released on 2026-04-09, added direct ALTER TABLE ... ALTER COLUMN ... SET NOT NULL syntax. Earlier SQLite versions need a different migration approach; consult the relevant version’s documentation before choosing one. SQLite: ALTER TABLE.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
