Inserting or updating a row with NULL in a column that requires a non-null value.
A NOT NULL constraint violation occurs when you try to insert or update a row with a `NULL` value in a column that has been defined as `NOT NULL`. The database enforces this constraint to ensure data completeness for required fields.
Causes: omitting a required column in an INSERT statement (it defaults to NULL), explicitly inserting NULL into a NOT NULL column, an application sending NULL for a mandatory field due to a bug or missing validation, or altering a nullable column to NOT NULL when existing rows have NULLs.
1CREATE TABLE orders (2 id INT PRIMARY KEY,3 customer_id INT NOT NULL,4 total_amount DECIMAL(10,2) NOT NULL,5 status VARCHAR(50) NOT NULL DEFAULT 'pending'6);7 8-- ERROR: customer_id is omitted — defaults to NULL9INSERT INTO orders (id, total_amount)10VALUES (1, 99.99);11-- ERROR: null value in column "customer_id" violates not-null constraint12 13-- ERROR: explicit NULL for NOT NULL column14INSERT INTO orders (id, customer_id, total_amount)15VALUES (2, NULL, 49.99);1-- Fix 1: Always provide all NOT NULL columns without defaults2INSERT INTO orders (id, customer_id, total_amount)3VALUES (1, 42, 99.99); -- customer_id provided4-- status defaults to 'pending' from DEFAULT clause5 6-- Fix 2: If column can be absent, add a DEFAULT or allow NULL7ALTER TABLE orders8 ALTER COLUMN customer_id SET DEFAULT 0; -- Placeholder value9 10-- Fix 3: Validate at application layer before SQL execution11-- (pseudocode)12-- if (order.customerId == null) throw new Error('customer_id is required');13 14-- Fix 4: Adding NOT NULL to existing table — handle existing NULLs first15UPDATE orders SET customer_id = 0 WHERE customer_id IS NULL;16ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;Simulate standard system builds to trigger compiler trace records and track memory crashes locally.
The first insert omits `customer_id`, which has no `DEFAULT` value and is `NOT NULL`. SQL sets omitted columns to `NULL` unless a default exists — causing the constraint violation. The second insert explicitly provides `NULL` for `customer_id`, which also violates the constraint.