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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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 |
I hope that helps someone.