NOT NULL constraint behavior in SQL - Deep Dive

Overview - NOT NULL constraint behavior

What is it?

The NOT NULL constraint in SQL ensures that a column cannot have empty or missing values. It forces every row in a table to have a value for that column. This helps keep data complete and reliable. Without it, important information might be accidentally left blank.

Why it matters

Without the NOT NULL constraint, databases could have missing data that causes errors or wrong results when you try to use that data. For example, if a customer’s email is missing, you can’t contact them. This constraint helps keep data trustworthy and prevents mistakes in applications that rely on the database.

Where it fits

Before learning about NOT NULL, you should understand basic SQL tables and columns. After this, you can learn about other constraints like UNIQUE, PRIMARY KEY, and FOREIGN KEY that also control data quality and relationships.

Mental Model

Core Idea

NOT NULL means a column must always have a value; it can never be left empty.

Think of it like...

Think of a form where some fields are marked with a red star, meaning you must fill them out before submitting. NOT NULL is like that red star on a database column.

┌───────────────┐
│   Table Row   │
├───────────────┤
│ Column A: 123 │
│ Column B: abc │  <-- NOT NULL means this can never be blank
│ Column C: NULL│  <-- This would cause an error if Column C is NOT NULL
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is NOT NULL Constraint

Concept: Introduce the basic idea of NOT NULL constraint in SQL.

In SQL, a NOT NULL constraint is a rule you add to a column to say it cannot have empty values. When you create or alter a table, you specify NOT NULL to make sure every row has a value in that column.

Result

The database will reject any attempt to insert or update a row with a missing value in that column.

Understanding NOT NULL is the first step to controlling data quality by preventing missing information.

2

Foundation

How to Define NOT NULL in SQL

Concept: Learn the syntax to add NOT NULL constraint when creating or modifying tables.

When creating a table, you write: CREATE TABLE users ( id INT NOT NULL, name VARCHAR(100) NOT NULL ); This means 'id' and 'name' must always have values. You can also add NOT NULL to existing columns using ALTER TABLE.

Result

The table enforces that these columns cannot be left empty in any row.

Knowing the syntax lets you apply NOT NULL constraints to protect important data fields.

3

Intermediate

NOT NULL and Default Values

🤔Before reading on: Do you think a NOT NULL column can accept NULL if it has a default value? Commit to yes or no.

Concept: Explore how default values interact with NOT NULL constraints.

If a NOT NULL column has a default value, the database uses that value when no value is provided during insert. This prevents errors from missing data. For example: CREATE TABLE products ( id INT NOT NULL, stock INT NOT NULL DEFAULT 0 ); If you insert a row without specifying 'stock', it will automatically be 0, not NULL.

Result

Inserts without explicit values for NOT NULL columns with defaults succeed using the default.

Understanding defaults with NOT NULL helps avoid insert errors and keeps data consistent.

4

Intermediate

NOT NULL Constraint on Updates

🤔Before reading on: Can you update a NOT NULL column to NULL? Commit to yes or no.

Concept: Learn that NOT NULL applies not only on insert but also on update operations.

If you try to update a NOT NULL column to NULL, the database will reject it with an error. For example: UPDATE users SET name = NULL WHERE id = 1; This will fail if 'name' is NOT NULL. You must provide a valid non-null value.

Result

Updates that try to set NOT NULL columns to NULL fail, protecting data integrity.

Knowing NOT NULL applies on updates prevents accidental data loss or corruption.

5

Intermediate

NOT NULL and NULL Differences

Concept: Clarify the difference between NULL and empty strings or zero values.

NULL means no value or unknown. It is different from an empty string ('') or zero (0). For example, a NOT NULL VARCHAR column can have '' but not NULL. This distinction is important for data meaning and queries.

Result

You can store empty strings or zeros in NOT NULL columns, but not NULL values.

Understanding NULL vs empty values helps design better data rules and avoid confusion.

6

Advanced

Changing Columns to NOT NULL Safely

🤔Before reading on: Can you add NOT NULL to a column that already has NULL values? Commit to yes or no.

Concept: Learn how to safely add NOT NULL constraints to existing columns with data.

You cannot add NOT NULL to a column if it contains NULLs. You must first update or delete rows with NULL values. For example: ALTER TABLE users ALTER COLUMN email SET NOT NULL; This will fail if any email is NULL. You fix this by: UPDATE users SET email = '[e-mail]' WHERE email IS NULL; Then add the constraint.

Result

NOT NULL constraints can be added only after cleaning data to remove NULLs.

Knowing this prevents errors and data loss when tightening data rules in production.

7

Expert

NOT NULL Constraint and Indexes Interaction

🤔Before reading on: Do NOT NULL constraints affect how indexes work? Commit to yes or no.

Concept: Explore how NOT NULL constraints influence indexing and query optimization.

Some database systems optimize indexes knowing a column is NOT NULL. For example, unique indexes can be simpler because NULLs are excluded. Also, query planners can skip NULL checks, improving performance. However, behavior varies by database engine.

Result

NOT NULL constraints can improve index efficiency and query speed in some systems.

Understanding this helps design faster databases by combining constraints and indexes wisely.

Under the Hood

When a NOT NULL constraint is defined, the database engine enforces it by checking every insert or update operation. Internally, it validates that the data value for that column is not NULL before committing the change. If the value is NULL, the operation is rejected immediately. This check happens at the storage engine level to ensure data integrity at all times.

Why designed this way?

NOT NULL was designed to prevent incomplete data entries that could cause errors or misinterpretations. Early databases allowed NULLs everywhere, which led to confusing results in queries and calculations. By enforcing NOT NULL, databases provide a simple, reliable way to guarantee essential data is always present. Alternatives like allowing NULLs everywhere were rejected because they made data handling more complex and error-prone.

┌───────────────┐
│ Insert/Update │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check NOT NULL│
│ constraint on │
│ column value  │
└──────┬────────┘
       │
  ┌────┴─────┐
  │          │
  ▼          ▼
Accept    Reject
Row       Operation
Insert    with Error
or Update

Myth Busters - 4 Common Misconceptions

Quick: Does NOT NULL mean the column cannot have empty strings? Commit to yes or no.

Common Belief: NOT NULL means the column cannot have empty strings or zero values.

Reality: NOT NULL only forbids NULL values, but empty strings ('') or zeros (0) are allowed as valid values.

Why it matters: Confusing NULL with empty values can lead to wrong data validation and unexpected query results.

Quick: Can you add NOT NULL to a column with existing NULL values without fixing data? Commit to yes or no.

Common Belief: You can add NOT NULL to any column anytime, even if it has NULLs.

Reality: You must first remove or replace NULL values before adding NOT NULL, or the database will reject the change.

Why it matters: Trying to add NOT NULL without cleaning data causes errors and blocks schema updates.

Quick: Does NOT NULL guarantee uniqueness of values? Commit to yes or no.

Common Belief: NOT NULL ensures all values in the column are unique.

Reality: NOT NULL only ensures values are present, not unique. Uniqueness requires a UNIQUE or PRIMARY KEY constraint.

Why it matters: Assuming NOT NULL means unique can cause duplicate data and logic errors.

Quick: Does NOT NULL constraint improve query performance by itself? Commit to yes or no.

Common Belief: NOT NULL constraints always make queries faster.

Reality: NOT NULL can help some optimizations, but by itself it does not guarantee faster queries. Indexes and query plans matter more.

Why it matters: Expecting performance gains from NOT NULL alone can lead to poor optimization choices.

Expert Zone

1

Some databases treat NOT NULL differently for certain data types, like JSON or arrays, which can affect how constraints are enforced.

2

In distributed databases, enforcing NOT NULL can involve coordination between nodes, impacting performance and consistency.

3

NOT NULL constraints can interact subtly with ORM frameworks, which may treat NULL and empty values differently in code versus database.

When NOT to use

Avoid NOT NULL when the data genuinely can be missing or unknown; instead, allow NULL and handle it in application logic. For optional data, use NULL or default values. For complex validation, consider CHECK constraints or triggers.

Production Patterns

In production, NOT NULL is commonly used on primary key columns, foreign keys, and essential fields like user IDs or timestamps. It is combined with defaults to ensure smooth inserts. Schema migrations carefully add NOT NULL only after cleaning data to avoid downtime.

Connections

Data Validation

NOT NULL is a basic form of data validation at the database level.

Understanding NOT NULL helps grasp how databases enforce rules to keep data clean before it reaches application code.

Error Handling in Programming

NOT NULL constraints cause errors when violated, similar to exceptions in programming.

Knowing how NOT NULL triggers errors helps developers write better error handling and data checks in their applications.

Human Forms and Mandatory Fields

NOT NULL is like mandatory fields in paper or online forms that must be filled before submission.

Recognizing this connection clarifies why databases need NOT NULL: to prevent incomplete records just like forms prevent incomplete submissions.

Common Pitfalls

#1 Trying to insert a row without a value for a NOT NULL column.

Wrong approach:

INSERT INTO users (id) VALUES (1);

Correct approach:

INSERT INTO users (id, name) VALUES (1, 'Alice');

Root cause:

Not providing a value for a column that requires one causes the database to reject the insert.

#2 Adding NOT NULL constraint to a column that already contains NULL values.

Wrong approach:

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Correct approach:

UPDATE users SET email = '[e-mail]' WHERE email IS NULL;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Root cause:

The database cannot enforce NOT NULL if existing data violates it; data must be cleaned first.

#3 Confusing NOT NULL with uniqueness and expecting no duplicates.

Wrong approach:

CREATE TABLE products (
sku VARCHAR(20) NOT NULL
); -- expecting unique SKUs

Correct approach:

CREATE TABLE products (
sku VARCHAR(20) NOT NULL UNIQUE
);

Root cause:

NOT NULL only prevents missing values, not duplicates; uniqueness requires a separate constraint.

Key Takeaways

NOT NULL constraint ensures that a column always has a value and never contains NULL.

It is essential for maintaining data completeness and preventing errors caused by missing information.

NOT NULL works on both inserting new rows and updating existing rows to keep data consistent.

Adding NOT NULL to existing columns requires cleaning data to remove NULLs first.

NOT NULL alone does not guarantee uniqueness or improve performance but is a fundamental building block for data integrity.

Practice

1. What does the NOT NULL constraint do in a database table?

easy

A. It allows a column to have duplicate values.

B. It ensures that a column cannot have empty or missing values.

C. It automatically fills the column with default values.

D. It deletes rows with null values in any column.

Solution

1. Step 1: Understand the purpose of NOT NULL

   The NOT NULL constraint prevents a column from having NULL (empty) values, ensuring data completeness.

2. Step 2: Compare options with the definition

   Only the description 'It ensures that a column cannot have empty or missing values.' correctly describes this behavior; others describe different constraints or actions.

3. Final Answer:

   It ensures that a column cannot have empty or missing values. -> Option B

4. Quick Check:

   NOT NULL = no empty values [OK]

Hint: NOT NULL means no empty values allowed in that column [OK]

Common Mistakes:

• Thinking NOT NULL allows duplicates
• Confusing NOT NULL with default values
• Assuming NOT NULL deletes rows

2. Which of the following is the correct syntax to add a NOT NULL constraint to a column named email in a table users?

easy

A. ALTER TABLE users MODIFY email NOT NULL;

B. ALTER TABLE users ADD email NOT NULL;

C. ALTER TABLE users SET email NOT NULL;

D. ALTER TABLE users CHANGE email TO NOT NULL;

Solution

1. Step 1: Recall ALTER TABLE syntax for modifying column constraints

   To add NOT NULL, we use ALTER TABLE with MODIFY (MySQL syntax).

2. Step 2: Evaluate each option's syntax

   ALTER TABLE users MODIFY email NOT NULL; uses correct syntax; others use invalid or incorrect commands.

3. Final Answer:

   ALTER TABLE users MODIFY email NOT NULL; -> Option A

4. Quick Check:

   ALTER TABLE + MODIFY + NOT NULL = correct syntax [OK]

Hint: Use ALTER TABLE with MODIFY to change NOT NULL [OK]

Common Mistakes:

• Using ADD instead of MODIFY for existing columns
• Using SET or CHANGE incorrectly
• Missing semicolon or wrong keywords

3. Given the table products with a price column defined as DECIMAL NOT NULL, what happens when you run this query?

INSERT INTO products (name, price) VALUES ('Pen', NULL);

medium

A. The query fails with an error due to NOT NULL violation.

B. The row is inserted with price set to 0.

C. The row is inserted with price set to NULL anyway.

D. The query succeeds but price is ignored.

Solution

1. Step 1: Understand NOT NULL effect on insert

   NOT NULL means the column cannot accept NULL values during insert or update.

2. Step 2: Analyze the insert statement

   Inserting NULL into a NOT NULL column causes the database to reject the query with an error.

3. Final Answer:

   The query fails with an error due to NOT NULL violation. -> Option A

4. Quick Check:

   Inserting NULL into NOT NULL column = error [OK]

Hint: Inserting NULL into NOT NULL column causes error [OK]

Common Mistakes:

• Assuming NULL becomes 0 automatically
• Thinking NULL is allowed silently
• Believing the query will partially succeed

4. You have this table creation statement:

CREATE TABLE employees (id INT, name VARCHAR(50) NOT NULL);

You try to insert:

INSERT INTO employees (id, name) VALUES (1, NULL);

But it gives an error. What is the fix?

medium

A. Add DEFAULT NULL to the name column.

B. Change the id column to NOT NULL.

C. Remove NOT NULL from the name column.

D. Insert a non-NULL value for name.

Solution

1. Step 1: Identify cause of error

   The name column is NOT NULL, so inserting NULL causes an error.

2. Step 2: Find correct fix

   To fix, insert a valid non-NULL value for name; changing other columns or constraints won't help here.

3. Final Answer:

   Insert a non-NULL value for name. -> Option D

4. Quick Check:

   NOT NULL column must get non-NULL value [OK]

Hint: Insert real value for NOT NULL columns to avoid errors [OK]

Common Mistakes:

• Removing NOT NULL without reason
• Changing unrelated columns
• Adding DEFAULT NULL which conflicts with NOT NULL

5. You want to ensure that the username column in your accounts table always has a value and cannot be empty. Which of these combined approaches is best to enforce this?

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  username VARCHAR(30) NOT NULL CHECK (username <> '')
);

hard

A. Use NOT NULL only, because it blocks empty strings too.

B. Use CHECK only, because NOT NULL allows empty strings.

C. Use both NOT NULL and CHECK to block NULL and empty strings.

D. No constraints needed; application code will handle it.

Solution

1. Step 1: Understand NOT NULL and empty strings

   NOT NULL prevents NULL values but allows empty strings ('').

2. Step 2: Understand CHECK constraint role

   CHECK (username <> '') prevents empty strings but allows NULL unless combined with NOT NULL.

3. Step 3: Combine constraints for full validation

   Using both NOT NULL and CHECK ensures username is neither NULL nor empty.

4. Final Answer:

   Use both NOT NULL and CHECK to block NULL and empty strings. -> Option C

5. Quick Check:

   NOT NULL + CHECK = no NULL or empty [OK]

Hint: Combine NOT NULL and CHECK to block NULL and empty strings [OK]

Common Mistakes:

• Assuming NOT NULL blocks empty strings
• Using CHECK without NOT NULL allows NULLs
• Relying only on application code