How to Fix “Cannot insert NULL into column Srno” When Srno Must Be Auto-Increment (SQL Server)

Sandhiya Priya
Mar 02
321
0
1

100

Article

When working with SQL Server tables, you may face this common error:

Cannot insert the value NULL into column 'Srno'. INSERT fails.

This usually happens when:

Srno is a PRIMARY KEY
Srno is NOT NULL
But Srno is not configured as auto-increment (IDENTITY)

To make it worse, you may also see:

Could not drop object 'SectionMaster' because it is referenced by a FOREIGN KEY constraint

This article explains why this happens and shows the correct production-safe solution step-by-step with examples and results.

Scenario

Original Table

CREATE TABLE SectionMaster (
    Srno INT NOT NULL PRIMARY KEY,
    Section NVARCHAR(255) NOT NULL,
    Descriptions NTEXT NOT NULL,
    Amount VARCHAR(50) NOT NULL,
    Logo VARCHAR(50) NOT NULL,
    active BIT NOT NULL
);

Insert Query

INSERT INTO SectionMaster (Section, Descriptions, Amount, Logo, active)
VALUES ('Derivatives', 'NSE Derivatives Section', '1000', 'nse.png', 1);

Error

Cannot insert the value NULL into column 'Srno'

Root Cause

Srno is:

NOT NULL
PRIMARY KEY
But NOT auto-increment (IDENTITY)

SQL Server expects a value, but your insert query does not provide one.

Why You Cannot Just Drop the Table

When you try:

DROP TABLE SectionMaster;

You get:

Could not drop object 'SectionMaster' because it is referenced by a FOREIGN KEY constraint.

Because another table depends on it:

FK_Name     : FK_Section  
ChildTable  : SubsectionMaster

Correct Production-Safe Solution (Without Changing Table Name)

Step 1 – Find Foreign Keys

SELECT 
    fk.name AS FK_Name,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('SectionMaster');

Result

FK_Section   | SubsectionMaster

Step 2 – Drop Foreign Key Temporarily

ALTER TABLE SubsectionMaster
DROP CONSTRAINT FK_Section;

Result: FK removed successfully.

Step 3 – Create Temporary Table with IDENTITY

CREATE TABLE SectionMaster_Temp (
    Srno INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Section NVARCHAR(255) NOT NULL,
    Descriptions NVARCHAR(MAX) NOT NULL,
    Amount VARCHAR(50) NOT NULL,
    Logo VARCHAR(50) NOT NULL,
    active BIT NOT NULL
);

Step 4 – Copy Existing Data (Preserve IDs)

SET IDENTITY_INSERT SectionMaster_Temp ON;

INSERT INTO SectionMaster_Temp (Srno, Section, Descriptions, Amount, Logo, active)
SELECT Srno, Section, Descriptions, Amount, Logo, active
FROM SectionMaster;

SET IDENTITY_INSERT SectionMaster_Temp OFF;

Result:
All old records moved with the same Srno.

Step 5 – Replace Table (Keep Same Name)

DROP TABLE SectionMaster;

EXEC sp_rename 'SectionMaster_Temp', 'SectionMaster';

Result:
Table name remains SectionMaster, but now Srno is auto-increment.

Step 6 – Recreate Foreign Key

ALTER TABLE SubsectionMaster
ADD CONSTRAINT FK_Section
FOREIGN KEY (Srno)
REFERENCES SectionMaster(Srno);

Result:
Relationship restored safely.

Final Test (Working Insert)

INSERT INTO SectionMaster (Section, Descriptions, Amount, Logo, active)
VALUES ('Equity', 'Equity Trading Section', '5000', 'equity.png', 1);

Output

Srno	Section	Descriptions	Amount	Logo	active
1	Equity	Equity Trading Section	5000	equity.png	1

Srno is now auto-generated

Key Learnings

Problem	Correct Fix
You can’t alter a column to IDENTITY	Recreate the table safely
Foreign key blocks DROP	Temporarily drop FK
Table name must stay same	Rename temp table back
NTEXT is deprecated	Use NVARCHAR(MAX)
Manual Srno insert	Avoid it

Final Table Design (Best Practice)

CREATE TABLE SectionMaster (
    Srno INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Section NVARCHAR(255) NOT NULL,
    Descriptions NVARCHAR(MAX) NOT NULL,
    Amount VARCHAR(50) NOT NULL,
    Logo VARCHAR(50) NOT NULL,
    active BIT NOT NULL
);