NOT NULL and DEFAULT
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)NOT NULL tells MySQL that a column must always have a value, and DEFAULT gives the value to use when an insert leaves the column out. Together they make sure every row is complete: no order without a customer, no product without a price.
NOT NULL: a value is required
NOT NULL is a rule on a column that refuses NULL, the marker SQL uses for no value or unknown. MySQL stops any INSERT or UPDATE that would leave the column empty.
Example 1 lets an empty address into a table where every column allows NULL. Example 2 adds NOT NULL, and the incomplete row is refused.
Example 1 · A nullable column lets a gap in
CREATE TABLE deliveries_loose ( delivery_id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(40), address VARCHAR(80));
DESCRIBE deliveries_loose;
INSERT INTO deliveries_loose (customer, address) VALUES ('Asha', '12 MG Road, Pune');INSERT INTO deliveries_loose (customer) VALUES ('Ravi'); -- address forgotten
SELECT * FROM deliveries_loose ORDER BY delivery_id;+-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | delivery_id | int | NO | PRI | NULL | auto_increment | | customer | varchar(40) | YES | | NULL | | | address | varchar(80) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ +-------------+----------+------------------+ | delivery_id | customer | address | +-------------+----------+------------------+ | 1 | Asha | 12 MG Road, Pune | | 2 | Ravi | NULL | +-------------+----------+------------------+
What this code does
DESCRIBEshowsYESunderNullforcustomerandaddress: both columns accept NULL.- The second insert leaves
addressout, and MySQL stores NULL without any complaint. The rider has nowhere to go.
Example 2 · NOT NULL refuses the gap
CREATE TABLE deliveries ( delivery_id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(40) NOT NULL, address VARCHAR(80) NOT NULL);
DESCRIBE deliveries;
INSERT INTO deliveries (customer, address) VALUES ('Asha', '12 MG Road, Pune');INSERT INTO deliveries (customer, address) VALUES ('Ravi', NULL);+-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | delivery_id | int | NO | PRI | NULL | auto_increment | | customer | varchar(40) | NO | | NULL | | | address | varchar(80) | NO | | NULL | | +-------------+-------------+------+-----+---------+----------------+ ERROR 1048 (23000): Column 'address' cannot be null
What this code does
DESCRIBEnow showsNOunderNullfor both columns.- Sending NULL for
addressfails with error 1048, so the incomplete delivery never reaches the table. The app must ask Ravi for an address first.
Write it after the column type: city VARCHAR(40) NOT NULL.
By default every column allows NULL. So when an insert leaves a column out, or a form sends nothing, MySQL quietly stores NULL. Later a delivery app finds an order with no address, or a report adds up marks and skips the blank ones. Example 1 shows how easily a gap gets in. Mark a column NOT NULL when a row makes no sense without it: a name, a price, an order date. Leave it nullable only when "unknown" is a real answer, like a phone number the customer did not give. In the practice database, customers.phone is the only customer column that allows NULL.
DESCRIBE shows the rule in its Null column: NO means NOT NULL, YES means NULL is allowed.DEFAULT values and DEFAULT in INSERT
DEFAULT is the value MySQL stores in a column when an insert does not give one. Write it after the type, as in status VARCHAR(12) NOT NULL DEFAULT 'PLACED'.
Example 3 leaves columns out of an INSERT, and MySQL fills them in. Example 4 shows that an explicit NULL is not replaced by the default.
Example 3 · Defaults fill the columns you leave out
CREATE TABLE web_orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(40) NOT NULL, status VARCHAR(12) NOT NULL DEFAULT 'PLACED', tip INT NOT NULL DEFAULT 0, order_date DATE NOT NULL DEFAULT (CURRENT_DATE), note VARCHAR(60));
INSERT INTO web_orders (customer) VALUES ('Meera');INSERT INTO web_orders (customer, tip, note) VALUES ('Kabir', 20, 'Gift box');INSERT INTO web_orders VALUES (DEFAULT, 'Divya', 'DELIVERED', DEFAULT, DEFAULT, DEFAULT);
SELECT order_id, customer, status, tip, note, order_date = CURRENT_DATE AS is_todayFROM web_ordersORDER BY order_id;+----------+----------+-----------+-----+----------+----------+ | order_id | customer | status | tip | note | is_today | +----------+----------+-----------+-----+----------+----------+ | 1 | Meera | PLACED | 0 | NULL | 1 | | 2 | Kabir | PLACED | 20 | Gift box | 1 | | 3 | Divya | DELIVERED | 0 | NULL | 1 | +----------+----------+-----------+-----+----------+----------+
What this code does
- Meera's insert gives only the customer, so
status,tipandorder_datetake their defaults.notehas no default and allows NULL, so it is NULL. - Divya's insert lists every column and writes
DEFAULTwhere it wants the default;DEFAULTin the id column lets AUTO_INCREMENT pick the id. - The date changes every day, so the query prints
order_date = CURRENT_DATE(1 means true) instead of the date itself.
Example 4 · A default does not replace an explicit NULL
CREATE TABLE web_orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(40) NOT NULL, tip INT NOT NULL DEFAULT 0);
INSERT INTO web_orders (customer) VALUES ('Meera');SELECT * FROM web_orders ORDER BY order_id;
-- The app sends NULL when the tip box is emptyINSERT INTO web_orders (customer, tip) VALUES ('Kabir', NULL);+----------+----------+-----+ | order_id | customer | tip | +----------+----------+-----+ | 1 | Meera | 0 | +----------+----------+-----+ ERROR 1048 (23000): Column 'tip' cannot be null
What this code does
- Leaving
tipout gives the default 0. Sending NULL on purpose is a different request, and NOT NULL refuses it with error 1048. - An application should leave the column out, or send
DEFAULT, when the user gave no value; it should not send NULL.
A default saves the application from sending the same obvious value every time.
The default can be a plain value (0, 'PLACED'), or an expression in brackets such as DEFAULT (CURRENT_DATE) for today's date (expression defaults need MySQL 8.0.13 or later). DATETIME and TIMESTAMP columns also accept DEFAULT CURRENT_TIMESTAMP without brackets. You get the default in two ways: leave the column out of the INSERT, or write the keyword DEFAULT in its place in VALUES. A nullable column with no DEFAULT of its own gets NULL.
Predict the output
What does the column hold?
orders.status is NOT NULL DEFAULT PLACED; customers.phone allows NULL and has no default.
Round 1 of 2
Strict mode and what happens without it
Strict mode is a server setting (STRICT_TRANS_TABLES in sql_mode) that makes MySQL refuse bad values with an error instead of quietly changing them. It has been on by default since MySQL 5.7.
Example 5 turns strict mode off for one session, and MySQL stores a made-up value instead of refusing the row.
Example 5 · Without strict mode MySQL makes up a value
SELECT @@sql_mode LIKE '%STRICT_TRANS_TABLES%' AS strict_on;
SET SESSION sql_mode = ''; -- strict mode off, this session only
CREATE TABLE hostel_fees ( roll_no INT PRIMARY KEY, name VARCHAR(40) NOT NULL, amount INT NOT NULL);
INSERT INTO hostel_fees (roll_no, name) VALUES (7, 'Gaurav'); -- amount left outSHOW WARNINGS;SELECT * FROM hostel_fees;+-----------+ | strict_on | +-----------+ | 1 | +-----------+ +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1364 | Field 'amount' doesn't have a default value | +---------+------+---------------------------------------------+ +---------+--------+--------+ | roll_no | name | amount | +---------+--------+--------+ | 7 | Gaurav | 0 | +---------+--------+--------+
What this code does
- The first query prints 1: strict mode is on by default. After
SET SESSION sql_mode = ''it is off for this connection. - The insert now succeeds.
amountgets the implicit default 0 and MySQL only records warning 1364, whichSHOW WARNINGSprints. - The table says Gaurav owes 0 rupees, which nobody typed. With strict mode on, the same insert fails with error 1364.
MySQL 8.0 and 8.4 both start with strict mode on.
Without strict mode, a NOT NULL column that an insert leaves out gets an "implicit default": an empty string for text, 0 for numbers. MySQL only adds a warning, a note the client prints as "1 warning" and that most applications never read. The row looks complete but holds made-up data. Some old setups, shared hosting servers and tutorials turn strict mode off. Check yours with SELECT @@sql_mode;, and keep STRICT_TRANS_TABLES in it. Example 5 turns it off for one session only, to show the danger.
SET SESSION sql_mode = '' changes only your current connection. It ends when you exit the client; never change it for the whole server.The cannot be null error
ERROR 1048 (Column cannot be null) is the error MySQL raises when a statement sends an explicit NULL to a NOT NULL column, in an INSERT or in an UPDATE.
Sending NULL to a NOT NULL column fails. The snippet shows the error, and Example 6 adds NOT NULL to a column that already holds gaps.
-- The column is left out and has no DEFAULTCREATE TABLE bank_accounts ( account_no VARCHAR(12) PRIMARY KEY, holder VARCHAR(40) NOT NULL, ifsc CHAR(11) NOT NULL);
INSERT INTO bank_accounts (account_no, holder) VALUES ('50100234567', 'Nisha Rao');ERROR 1364 (HY000): Field 'ifsc' doesn't have a default value
Example 6 · Adding NOT NULL to a column that has NULLs
CREATE TABLE patients_old ( patient_id INT PRIMARY KEY, name VARCHAR(40), blood_group VARCHAR(3));
INSERT INTO patients_old VALUES (1, 'Kavya', 'B+');INSERT INTO patients_old VALUES (2, 'Imran', NULL);
ALTER TABLE patients_old MODIFY blood_group VARCHAR(3) NOT NULL;ERROR 1138 (22004): Invalid use of NULL value
What this code does
- Imran's row already has NULL in
blood_group, so MySQL cannot promise "never NULL" and stops with error 1138. - Fill the gap first, for example
UPDATE patients_old SET blood_group = 'NA' WHERE blood_group IS NULL;, then run the ALTER again (practice 6). The Changing Data module covers UPDATE in full.
Error 1048 appears in an UPDATE too, for example UPDATE ... SET price = NULL.
A close cousin is ERROR 1364 (HY000): Field 'x' doesn't have a default value. Here the INSERT left a NOT NULL column out and the column has no DEFAULT, so MySQL has nothing to store. The fix for 1048 is to send a real value; the fix for 1364 is to send one or to add a sensible DEFAULT. Adding NOT NULL to a column that already holds NULLs fails with ERROR 1138 (22004): Invalid use of NULL value (Example 6).
Next step
Check your understanding
11 quick questions