# 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:**
    
    ```typescript
    @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`
    
    ```sql
    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)

```sql
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**

```sql
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
    
    ```sql
    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 😊.