📋

Description

SQL Server Error 515 indicates an attempt to insert a NULL value into a column that is defined as NOT NULL. This typically occurs during INSERT or UPDATE operations when a required column is missing a value.

💬

Error Message

Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

🔍

Known Causes

4 known causes

⚠️

Missing Column Value

The INSERT statement does not include a value for a NOT NULL column, resulting in a NULL insertion attempt.

⚠️

Incorrect Data Mapping

Data being imported or transformed is mapped incorrectly, leading to NULL values being assigned to NOT NULL columns.

⚠️

Application Logic Error

The application code is unintentionally passing NULL values to required database columns.

⚠️

Trigger/Procedure Issues

A trigger or stored procedure is attempting to insert NULL into a column that doesn't allow nulls.

🛠️

Solutions

4 solutions available

1. Provide Value for NOT NULL Column easy

Include all required columns in INSERT

1

Find NOT NULL columns

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
AND IS_NULLABLE = 'NO'
ORDER BY ORDINAL_POSITION;

2

Include required columns

-- Wrong: missing NOT NULL column
INSERT INTO Employees (name) VALUES ('John');

-- Correct: include all required columns
INSERT INTO Employees (name, email, hire_date)
VALUES ('John', 'john@example.com', GETDATE());

2. Add Default Value to Column medium

Let column auto-populate if not specified

1

Add default constraint

-- Add default value:
ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_Status
DEFAULT 'Active' FOR status;

-- Now INSERT without status will use default:
INSERT INTO Employees (name) VALUES ('John');
-- status will be 'Active'

2

Create table with defaults

CREATE TABLE Employees (
    id INT IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    created_at DATETIME NOT NULL DEFAULT GETDATE()
);

3. Handle NULL in Source Data easy

Use COALESCE or ISNULL when inserting from other sources

1

Replace NULL with default

INSERT INTO Employees (name, department)
SELECT 
    name,
    ISNULL(department, 'Unknown')  -- Replace NULL
FROM StagingTable;

2

Use COALESCE for multiple fallbacks

INSERT INTO Employees (email)
SELECT 
    COALESCE(work_email, personal_email, 'noemail@company.com')
FROM Contacts;

4. Make Column Nullable medium

Change column to allow NULL if appropriate

1

Alter column to allow NULL

-- Remove NOT NULL constraint:
ALTER TABLE Employees
ALTER COLUMN middle_name VARCHAR(50) NULL;

SQL Server Error 515: Null Insertion Failed