Why SQLite UNIQUE allows multiple NULLs: Compared with PRIMARY KEY

You add UNIQUE to an email column to prevent duplicates. Should that also allow only one account without an email? In SQLite, two rows with NULL email both insert successfully. The constraint is working: a repeated email value and the absence of an email value are different cases.

Why can UNIQUE accept two NULL rows?

Here id identifies each account, and email is an optional unique value. NULL marks missing or unknown data; it is not the empty string ''.

The diagram separates row identity from email uniqueness. Two missing emails can coexist, but an existing concrete email cannot be inserted again.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE
);

INSERT INTO accounts (id, email) VALUES
  (1, NULL),
  (2, NULL),
  (3, '[email protected]');

SELECT id, email FROM accounts ORDER BY id;

SQLite treats NULL entries as distinct for a UNIQUE constraint, so UNIQUE(email) does not mean “email must be present.” This article describes SQLite behavior; check another DBMS's rules before relying on identical treatment there.

Why do a repeated email and empty string behave differently?

Insert the same real email again:

INSERT INTO accounts (id, email)
VALUES (4, '[email protected]');
-- UNIQUE constraint failed: accounts.email

The fourth row is rejected because its email equals the existing value. An empty string is also a real string value, even though it can look blank on screen. Insert email='' twice and the second insert violates the same uniqueness constraint. Mixing NULL and '' for “no email” gives different database behavior.

Why does GROUP BY show the NULL rows together?

A count grouped by email displays two NULL rows as one group:

SELECT email, COUNT(*) AS rows_in_group
FROM accounts
GROUP BY email;

Grouping rows for an aggregate and enforcing uniqueness use different rules. This group does not prove the constraint is broken. To inspect duplicate known emails, exclude missing values:

SELECT email, COUNT(*) AS n
FROM accounts
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
-- No rows in this example.

If email is required, add NOT NULL

Separate the requirements: NOT NULL blocks absence; UNIQUE blocks reuse of a concrete value.

CREATE TABLE required_accounts (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

INSERT INTO required_accounts (id, email) VALUES (1, NULL);
-- NOT NULL constraint failed: required_accounts.email

Even NOT NULL does not reject '' or whitespace-only text. Whether differently cased emails count as the same address is another normalization and collation decision. UNIQUE alone is not a complete signup policy.

Is PRIMARY KEY the same as UNIQUE?

In this example, id INTEGER PRIMARY KEY identifies each row. Reusing id = 1 fails even when the email is NULL. email UNIQUE independently protects a field value; a table can have multiple unique constraints but one primary-key definition.

SQLite has an exception for some non-integer primary-key declarations in ordinary tables: they may allow NULL. This example uses INTEGER PRIMARY KEY, which behaves differently. SQLite's CREATE TABLE constraints documentation describes the exception. Do not generalize one key form to every SQLite table.

Key takeaways

In SQLite, UNIQUE(email) rejects duplicate email strings but accepts multiple NULLs. NULL differs from '', and a GROUP BY count of missing values does not imply a uniqueness violation. For a required email, combine NOT NULL UNIQUE; use a separate primary key to identify each row.