Add NOT NULL column to existing table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Add NOT NULL column to existing table

Hi all, Is there a way to add a column that does not allow nulls and does not have a default to an existing table: alter TABLE [dbo].[MyTable]
add MyColumn varchar (100) Not NULL
GO Any help is appreciated.
No. Either you add the column with allowing NULL’s, populate the data make sure there are no NULL values and then enfore the constraint.
***********************
Dinakar Nethi
SQL Server MVP
***********************
another way,
Add a coumn with a default constraint ADD AddDate smalldatetime NOT NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES then drop the constraint Drop CONSTRAINT AddDateDflt
—————————————-
that sounds more like it, because NOT NULL is a property,and will not allow me to change the column from NULL to NOT NULL. But I can create a default and then drop it, thanks!
]]>