SQL: Make column NOT NULL but don't check existing data in SQL Server

A forum poster was asking how to make a SQL Server column NOT NULL, but not apply it to old data. But when you add NOT NULL, there's no option to say "Just new data". However, you can work around it to achieve the same goal.

When you add a constraint to SQL Server, you can say WITH NOCHECK. That says to not check existing data. You can add a constraint to ensure a value isn't NULL.

Here's an example:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Test;

CREATE TABLE dbo.Test
(
    TestID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_Test PRIMARY KEY,
    TestName varchar(50) NULL
);
GO

INSERT dbo.Test (TestName)
VALUES ('Fred'), (NULL), ('Mary');
GO

ALTER TABLE dbo.Test
WITH NOCHECK ADD CONSTRAINT CK_dbo_Test_TestName_not_null
CHECK (TestName IS NOT NULL);
GO

-- Will work
INSERT dbo.Test (TestName)
VALUES ('Mia');
GO

-- Won't work
INSERT dbo.Test (TestName)
VALUES (NULL);
GO

-- Old data still OK
SELECT *
FROM dbo.Test;
GO

USE tempdb;

DROP TABLE IF EXISTS dbo.Test;

CREATE TABLE dbo.Test

(

TestID int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_dbo_Test PRIMARY KEY,

TestName varchar(50) NULL

);

INSERT dbo.Test (TestName)

VALUES ('Fred'), (NULL), ('Mary');

ALTER TABLE dbo.Test

WITH NOCHECK ADD CONSTRAINT CK_dbo_Test_TestName_not_null

CHECK (TestName IS NOT NULL);

-- Will work

INSERT dbo.Test (TestName)

VALUES ('Mia');

-- Won't work

INSERT dbo.Test (TestName)

VALUES (NULL);

-- Old data still OK

SELECT *

FROM dbo.Test;

I hope that helps someone.

2 thoughts on “SQL: Make column NOT NULL but don't check existing data in SQL Server”

Thanks, Doc… You've saved me some time.

greglowblog says:

August 30, 2023 at 11:52 am

Always happy to see that ! Hope you're well.

Reply

2 thoughts on “SQL: Make column NOT NULL but don't check existing data in SQL Server”

Leave a Reply Cancel reply