NOT NULL constraint failed

The NOT NULL constraint failed error occurs when you try to insert NULL into a column that's marked as NOT NULL. SQLite is protecting your data integrity.

Understanding the Error

SQLITE_CONSTRAINT: NOT NULL constraint failed: users.email

This tells you:

1. The users table has a NOT NULL constraint on email
2. You tried to insert a row without providing an email value
3. The operation was rejected

Common Causes

1. Missing Required Field

Not providing a value for a required column:

-- email is NOT NULL
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT NOT NULL
);

-- Fails: no email provided
INSERT INTO users (name) VALUES ('Alice');

2. Explicitly Inserting NULL

Directly inserting NULL into a NOT NULL column:

INSERT INTO users (name, email) VALUES ('Alice', NULL);

3. Application Sending Null Values

Your application code might be sending null:

// If email is undefined/null, this fails
db.run('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]);

4. Update Setting Column to NULL

Updates can also trigger this:

UPDATE users SET email = NULL WHERE id = 1;

How to Fix It

Solution 1: Provide the Required Value

Simply include the required field:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Solution 2: Set a Default Value

Modify the table to have a default:

-- For new tables
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT NOT NULL DEFAULT 'unknown@example.com'
);

-- For existing tables (requires recreating)
-- SQLite doesn't support ALTER COLUMN, so:
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT NOT NULL DEFAULT 'unknown@example.com'
);
INSERT INTO users_new SELECT * FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Solution 3: Remove the NOT NULL Constraint

If the column should allow NULL values:

-- Recreate without NOT NULL
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT  -- Now nullable
);
INSERT INTO users_new SELECT * FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Solution 4: Handle Nulls in Application Code

Validate and provide defaults before inserting:

function insertUser(name, email) {
  // Provide default if email is missing
  const safeEmail = email || 'not-provided@example.com';

  db.run(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, safeEmail]
  );
}

Solution 5: Use COALESCE in SQL

Provide a fallback value in your query:

INSERT INTO users (name, email)
VALUES ('Alice', COALESCE(?, 'default@example.com'));

Checking NOT NULL Columns

Find which columns have NOT NULL constraints:

PRAGMA table_info(users);

Output shows notnull column (1 = NOT NULL, 0 = nullable):

cid  name   type     notnull  dflt_value  pk
---  -----  -------  -------  ----------  --
0    id     INTEGER  0        NULL        1
1    name   TEXT     0        NULL        0
2    email  TEXT     1        NULL        0

Best Practices

1. Design schemas carefully - Only use NOT NULL when truly required
2. Use DEFAULT values for NOT NULL columns when sensible
3. Validate in application code before database operations
4. Document required fields in your API/schema documentation
5. Handle edge cases - What if user doesn't provide email?

Common Patterns

Optional Fields with NOT NULL

Use empty string instead of NULL for optional text:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  bio TEXT NOT NULL DEFAULT ''  -- Empty string, not NULL
);

Timestamps with Defaults

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Related Errors

• UNIQUE constraint failed - Duplicate value in unique column
• FOREIGN KEY constraint failed - Referenced row doesn't exist
• CHECK constraint failed - Value doesn't meet validation rule