How to add a NOT NULL column to a SQL Server table that already has rows
The column is new, every row should have a value, so it is NOT NULL:
ALTER TABLE dbo.Customer ADD Region varchar(20) NOT NULL;
On an empty table that works. On a table with rows, SQL Server refuses:
Msg 4901, Level 16, State 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition
specified, or the column being added is an identity or timestamp column, or alternatively if none
of the previous conditions are satisfied the table must be empty to allow addition of this column.
The existing rows need a value in the new column, and nothing has said what it should be. The message lists the ways out.
Option 1: give it a default
If new rows should get a value automatically anyway, add the column with a default. SQL Server fills the existing rows with it as part of the same statement:
ALTER TABLE dbo.Customer
ADD Region varchar(20) NOT NULL
CONSTRAINT DF_Customer_Region DEFAULT ('Unassigned');
Name the constraint. An unnamed one gets a generated name that differs on every database, and dropping the column later then needs a lookup.
On SQL Server 2012 and later Enterprise Edition, and on Azure SQL, adding a NOT NULL column with a
constant default is a metadata-only change, fast even on a very large table. Other editions write
every row.
Option 2: fill it, then keep no default
Often you want the existing rows filled but no default afterwards, because from now on the application must supply the value. Add the column with a default, then drop the default straight away:
ALTER TABLE dbo.Customer
ADD Region varchar(20) NOT NULL
CONSTRAINT DF_Customer_Region_fill DEFAULT ('');
ALTER TABLE dbo.Customer DROP CONSTRAINT DF_Customer_Region_fill;
The rows keep the value they were given. The column ends up exactly as designed: NOT NULL, no
default, and an insert that leaves it out fails, which is the point.
The fill does not have to be a constant. DEFAULT (NEWID()) gives each existing row its own GUID,
and DEFAULT (SYSDATETIME()) stamps them all with the current date and time.
Option 3: nullable first, then tighten
When the right value differs from row to row, for example a region worked out from each customer's
address, add the column as nullable, fill it properly, then make it NOT NULL:
ALTER TABLE dbo.Customer ADD Region varchar(20) NULL;
GO
UPDATE c
SET Region = a.Region
FROM dbo.Customer AS c
JOIN dbo.PostcodeRegion AS a ON a.Postcode = c.Postcode;
UPDATE dbo.Customer SET Region = 'Unassigned' WHERE Region IS NULL;
GO
ALTER TABLE dbo.Customer ALTER COLUMN Region varchar(20) NOT NULL;
The GO after the ADD is not decoration. SQL Server compiles a whole batch before it runs any of
it, and at compile time the new column does not exist yet, so an UPDATE in the same batch fails
with Invalid column name.
On a large table, do the UPDATE in batches to keep the transaction log and the locks under
control, and check nothing is left before the final ALTER:
SELECT COUNT(*) FROM dbo.Customer WHERE Region IS NULL; -- must be 0
Letting the script do it
Add a NOT NULL column to an existing table in WoodFireERD and the migration script handles
the existing rows for you. If the column has a default, the default fills them. If it has none, the
script uses option 2: blank text, zero, the current date and time, or a new GUID for each row,
depending on the column's type. Then it removes the temporary default so the column ends up as you
designed it. Each fill is listed in Pending changes with the value it writes, so you can see it
before you export.