How to fix SQLITE_CONSTRAINT_NOTNULL: NOT NULL constraint failed in SQLite

SQLITE_CONSTRAINT_NOTNULL: NOT NULL constraint failed: users.email

-- View the table structure
.schema users

-- Or use PRAGMA for detailed info
PRAGMA table_info(users);

cid  name     type        notnull  dflt_value  pk
---  -------  ----------  -------  ----------  --
0    id       INTEGER     1        NULL        1
1    email    TEXT        1        NULL        0    <-- NOT NULL constraint
2    name     TEXT        0        NULL        0    <-- Allows NULL

-- Missing required column
INSERT INTO users (name) VALUES ('John');
-- Error: email column is NOT NULL but not provided

-- Explicit NULL in NOT NULL column
INSERT INTO users (email, name) VALUES (NULL, 'John');
-- Error: email cannot be NULL

-- Column count mismatch
INSERT INTO users VALUES ('John');
-- Error: expects 3 columns (id, email, name) but only 1 provided

-- Setting NOT NULL column to NULL
UPDATE users SET email = NULL WHERE id = 1;
-- Error: email cannot be NULL

-- Using NULL in WHERE clause affecting NOT NULL column
UPDATE users SET name = 'Jane' WHERE email IS NULL;
-- May fail if email is NOT NULL and has no NULL values

-- Correct INSERT with all NOT NULL columns
INSERT INTO users (email, name) VALUES ('[email protected]', 'John');

-- Or use DEFAULT if defined
INSERT INTO users (email) VALUES ('[email protected]');
-- name can be NULL or use default if defined

// Model definition
const User = sequelize.define('User', {
  email: {
    type: DataTypes.STRING,
    allowNull: false, // NOT NULL constraint
  },
  name: {
    type: DataTypes.STRING,
    allowNull: true, // Allows NULL
  }
});

// Problematic create
await User.create({ name: 'John' });
// Error: email is required (allowNull: false)

// Correct create
await User.create({
  email: '[email protected]',
  name: 'John'
});

from sqlalchemy import Column, String, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    email = Column(String, nullable=False)  # NOT NULL
    name = Column(String, nullable=True)    # Allows NULL

# Problematic insert
session.add(User(name='John'))
session.commit()  # Error: email is required

# Correct insert
session.add(User(email='[email protected]', name='John'))
session.commit()

@Entity
@Table(name = "users")
public class User {
    @Column(nullable = false)  // NOT NULL
    private String email;

    @Column(nullable = true)   // Allows NULL
    private String name;

    // Getters and setters
}

// Ensure all @NotNull fields are set before persisting
User user = new User();
user.setEmail("[email protected]");
user.setName("John");
entityManager.persist(user);

-- Current schema (causing errors)
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,  -- No default, always required
  name TEXT
);

-- Add default value
ALTER TABLE users ADD COLUMN email TEXT NOT NULL DEFAULT '[email protected]';

-- Or allow NULL (if appropriate)
ALTER TABLE users ADD COLUMN email TEXT;  -- Allows NULL

-- 1. Create temporary table without NOT NULL
CREATE TABLE users_temp (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT
);

-- 2. Copy data
INSERT INTO users_temp SELECT * FROM users;

-- 3. Drop original table
DROP TABLE users;

-- 4. Recreate with proper constraints
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL DEFAULT '[email protected]',
  name TEXT
);

-- 5. Copy data back, filling NULLs with default
INSERT INTO users (id, email, name)
SELECT
  id,
  COALESCE(email, '[email protected]'),
  name
FROM users_temp;

-- 6. Drop temporary table
DROP TABLE users_temp;

-- Use COALESCE or IFNULL in INSERT statements
INSERT INTO users (email, name)
VALUES (
  COALESCE(:email, '[email protected]'),
  :name
);

function validateUserData(data) {
  const errors = [];

  if (!data.email || data.email.trim() === '') {
    errors.push('Email is required');
  }

  // Additional validation
  if (data.email && !isValidEmail(data.email)) {
    errors.push('Email format is invalid');
  }

  return errors;
}

// Usage
const userData = { name: 'John' };
const validationErrors = validateUserData(userData);
if (validationErrors.length > 0) {
  console.error('Validation failed:', validationErrors);
  // Don't attempt database insert
}

from pydantic import BaseModel, EmailStr, ValidationError
from typing import Optional

class UserCreate(BaseModel):
    email: EmailStr  # Required, validated email
    name: Optional[str] = None  # Optional

# Validation
try:
    user_data = UserCreate(email="[email protected]", name="John")
    # Proceed with database insert
except ValidationError as e:
    print("Validation failed:", e.errors())

-- Add CHECK constraint alongside NOT NULL
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL CHECK(email != ''),
  name TEXT
);

-- Or for more complex validation
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL CHECK(
    email LIKE '%@%.%' AND
    length(email) BETWEEN 5 AND 255
  ),
  name TEXT
);

-- Use transaction to rollback on error
BEGIN TRANSACTION;

INSERT INTO users (email, name) VALUES
  ('[email protected]', 'User 1'),
  ('[email protected]', 'User 2'),
  -- Missing email will cause rollback
  (NULL, 'User 3'),
  ('[email protected]', 'User 4');

COMMIT;
-- If any row fails, entire transaction rolls back

import sqlite3

def batch_insert_users(users_data):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()

    successful = 0
    failed = []

    for user in users_data:
        try:
            # Validate required fields
            if not user.get('email'):
                raise ValueError("Email is required")

            cursor.execute(
                "INSERT INTO users (email, name) VALUES (?, ?)",
                (user['email'], user.get('name'))
            )
            successful += 1
        except (sqlite3.IntegrityError, ValueError) as e:
            failed.append({
                'user': user,
                'error': str(e)
            })

    conn.commit()
    conn.close()

    return successful, failed

# Usage
users = [
    {'email': '[email protected]', 'name': 'User 1'},
    {'name': 'User 2'},  # Missing email - will fail
    {'email': '[email protected]', 'name': 'User 3'},
]

successful, failed = batch_insert_users(users)
print(f"Successful: {successful}, Failed: {len(failed)}")

-- When migrating data with potential NULLs in NOT NULL columns
INSERT INTO new_users (id, email, name)
SELECT
  id,
  COALESCE(email, '[email protected]') as email,
  name
FROM old_users;