Skip to content

SQL, Handling empty cells

The Valley of Code

Your Web Development Manual

How to handle null data in a SQL database

When we create a table in this way:

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

SQL freely accepts empty values as records:

INSERT INTO people VALUES (null, null);

This might be a problem, because now we have a row with null values:

 age |  name  
-----+--------
  37 | Flavio
   8 | Roger
     | 

To solve this, we can declare constrains on our table rows. NOT NULL prevents null values:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL
);

If we try to execute this query again:

INSERT INTO people VALUES (null, null);

We’d get an error, like this:

ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (null, null).

Note that an empty string is a valid non-null value.

→ Read my SQL Tutorial on The Valley of Code
  • THE VALLEY OF CODE (+ PRO), your web development manual
  • I wrote 15+ free coding BOOKS, download them here
  • SOLOPRENEUR LAND the missing MBA for wannabe solopreneurs craving a life with more freedom, control, fulfillment and purpose (summer 2024)