If a table has DEFAULT 'NEW' but inserting NULL still stores a null value or raises an error, you may be treating the default as a way to repair every empty field. DEFAULT supplies a value when a column is omitted; NOT NULL determines whether a null value is allowed.
DEFAULT applies when an INSERT omits the column
The diagram separates the omitted status column from a supplied value. An explicit NULL is not replaced by this ordinary default and fails the NOT NULL constraint.
This example uses Oracle's NUMBER and VARCHAR2 types. Use the corresponding type names for another database product.
CREATE TABLE orders (
id NUMBER PRIMARY KEY,
customer VARCHAR2(100) NOT NULL,
status VARCHAR2(20) DEFAULT 'NEW' NOT NULL
);
INSERT INTO orders (id, customer)
VALUES (1, 'Customer A');
SELECT id, status FROM orders WHERE id = 1;The INSERT omits status, so the query should return 1, NEW. A default is useful for setting an initial state in one place. It does not retroactively rewrite existing rows or override a value explicitly sent by the application.
Explicit NULL produces a different result
This INSERT supplies NULL for status:
INSERT INTO orders (id, customer, status)
VALUES (2, 'Customer B', NULL);Because status is NOT NULL, the statement should fail. That failure stops a row without an order state at the database boundary instead of silently saving it. Oracle also offers a separate DEFAULT ON NULL form; the example uses ordinary DEFAULT, so the distinction matters.
Test both outcomes. After the first INSERT, confirm status = 'NEW'. For the second, confirm the constraint error and then check that no row with id = 2 remains. An error message alone may not tell you whether a surrounding tool retried or handled the statement in an unexpected way.
Without NOT NULL, an explicit null can be stored depending on the database and syntax. Declare “use a default when omitted” and “do not permit NULL” separately when both are intended.
A default does not encode every business rule
If states include NEW, PAID, and CANCELED, a default does not prevent invalid transitions. Validate state changes in the application service and consider a CHECK constraint or separate state table when appropriate. The default specifies a starting point, not the sequence of allowed changes.
Time defaults need similar care. A database current-time expression can initialize a creation timestamp, but its time zone and responsibility for updating another timestamp need separate decisions. Server and database time zones that differ can complicate log comparisons.
Check existing data before changing a schema
Adding a NOT NULL column to a populated table requires a plan for old rows. Decide what value those rows should receive and whether that value is actually true. Filling historical records with an arbitrary default can change their meaning.
Test a migration with existing rows, explicit null input, and omitted-column input—not only against an empty database.
Key takeaways
An ordinary DEFAULT supplies an initial value when an INSERT omits a column; NOT NULL rejects null storage. Omitting a column differs from explicitly inserting NULL. Review existing data and migration order before adding these rules to a live table.
