TIL: Debugging "Column Cannot Be Null" Error in MySQL despite nullable column definition

Problem Statement

While working on a Nestjs project, I encountered a weird problem related to the database column. I was trying to insert a record into a MySQL table using TypeORM. The error I was experiencing stated that a specific column “cannot be null”, even though the column was explicitly defined as nullable.

Investigation & Root Cause

I performed the following checks to identify the root cause:

1. Checked ORM Entity & Column Definition:

    @Column({ type: 'int', nullable: true })
    columnName?: number | null;
   

   The column was correctly marked as nullable in the ORM model.

2. Verified Database Schema using SHOW CREATE TABLE

    CREATE TABLE `table_name` (
      `columnName` int DEFAULT NULL,
      ...
    );
   

   To make sure everything is good at the table structure level, I ran this command SHOW CREATE TABLE TABLE_NAME. This command gives the details of the table and how it is structured. The definition explicitly stated that the column could be null.

3. Checked for Foreign Key Constraints using SHOW CREATE TABLE & SHOW TABLE STATUS

   No explicit foreign key constraint was found enforcing a non-null value.

4. Checked Active Triggers using SHOW TRIGGERS WHERE Table = 'table_name';

   Discovered one or more triggers (e.g., table_name_insert, table_name_update) that were modifying data before the insert/update operation.

5. Issue Identified:

   • A trigger was enforcing a non-null constraint on the column, even though the schema allowed NULL values.

Solution

Option 1: Remove the Trigger (if unnecessary)

DROP TRIGGER trigger_name;

The easiest option to fix this problem was to remove the trigger, which is causing the problem.

Option 2: Modify the Trigger to Handle Null Values Properly

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
  IF NEW.columnName IS NOT NULL THEN
    INSERT INTO log_table (columnName, action)
    VALUES (NEW.columnName, 'INSERT');
  END IF;
END;

We can add a safety check to prevent errors when the inserted record is NULL.

Option 3: Allow NULL values in the log table

We can modify the column definition in the target log_table to allow NULLs.

Conclusion:

• Even if a column is nullable, triggers can override this behavior.

• Always check active triggers using

    SHOW TRIGGERS WHERE `Table` = 'table_name';
  

• Modifying or disabling triggers can resolve unexpected Column Cannot Be Null errors.

I hope you like this small TIL blog. If you have any questions, please comment below. Thanks for reading 😊.