CODEWITHSUNDEEP
sqlsql-server-2017
Tat Scagliotti
Tat Scagliotti

Reputation: 69

Cannot insert the value NULL into column X, column X does not allow nulls. INSERT fails.

I'm new to SQL Server and I am getting this error "Cannot insert the value NULL into column 'Occupied', table 'DBProjectHamlet.dbo.tblGrave'; column does not allow nulls. INSERT fails. The statement has been terminated."

This is my code for the insert followed by the code to create the table 

INSERT INTO tblGrave (GraveName)
SELECT Grave
FROM tblPlotsandOccupants


IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblGrave' AND TABLE_SCHEMA = 'dbo')
    DROP TABLE dbo.tblGrave;
    GO
CREATE TABLE tblGrave
(
GraveID INT IDENTITY (1,1),
GraveName VARCHAR(MAX) NULL,
GraveTypeID INT NOT NULL,
PlotID INT NOT NULL,
Occupied BIT NOT NULL
)

I'm not trying to insert anything into column Occupied, I don't know why this is happening or how to fix it. I just want to insert values into tblGrave (GraveName). Any help would be great.

Upvotes: 2

Views: 44827

Answers (2)

Bigbob556677
Bigbob556677

Reputation: 2158

When you created your table, you defined that column as "NOT NULL" rather than allowing it to be null.

You need to either allow "Occupied" to be null, set a default value, or define the value that you want upon inserting.

You can even set the value to be ' ' which is blank but isn't null.

EDIT

Note @Gordon's answer for sql examples.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Exactly! You aren't doing anything with Occupied and that is the problem. The column is specified to be NOT NULL but has no default value. You are not inserting a value, so it gets the default. The default default is NULL, and that is not allowed.

One simple solution is:

INSERT INTO tblGrave (GraveName, Occupied)
    SELECT Grave, 0
    FROM tblPlotsandOccupants;

This fixes your immediate problem, but will then you will get an error on PlotId.

A more robust solution would add a default value for the NOT NULL columns and declare the rest to be nullable (the default). Something like this:

CREATE TABLE tblGrave (
    GraveID INT IDENTITY (1,1) PRIMARY KEY,
    GraveName VARCHAR(MAX),
    GraveTypeID,
    PlotID INT,
    Occupied BIT NOT NULL DEFAULT 0
);

Upvotes: 6

Related Questions