Changing a column from NULL to NOT NULL in SQL Server
The column should never have allowed NULL, and now you want to enforce it:
ALTER TABLE dbo.[Order] ALTER COLUMN ShippedBy nvarchar(50) NOT NULL;
If any row holds NULL in that column, SQL Server refuses:
Msg 515, Level 16, State 2
Cannot insert the value NULL into column 'ShippedBy', table 'Sales.dbo.Order';
column does not allow nulls. UPDATE fails.
The statement has been terminated.
It says insert and UPDATE because changing the column means checking every existing value against the new rule, and some fail. Nothing was changed.
Step 1: find out how many
SELECT COUNT(*) FROM dbo.[Order] WHERE ShippedBy IS NULL;
The number decides the approach. Three rows might be data-entry mistakes worth fixing by hand. Three hundred thousand means the column has been genuinely optional, and something has to decide what those rows mean now.
Step 2: fill them
If the column has a default, that is usually the right value, because it is what a new row would get:
UPDATE dbo.[Order] SET ShippedBy = N'Unknown' WHERE ShippedBy IS NULL;
Otherwise pick something honest. An empty string, zero, or a clearly marked placeholder all work,
but think about how the value will be read later. A 0 in a quantity column that meant not
recorded will be summed as a real zero by the next report.
Step 3: tighten the column
ALTER TABLE dbo.[Order] ALTER COLUMN ShippedBy nvarchar(50) NOT NULL;
Repeat the full type exactly. ALTER COLUMN replaces the column's definition, so writing
nvarchar without the length gives you nvarchar(1), and every longer value makes the statement
fail.
If the type is changing at the same time
Change the type first, while the column still allows NULL, then fill, then tighten. That way the fill value is written as the new type rather than converted from the old one:
ALTER TABLE dbo.[Order] ALTER COLUMN ShippedBy nvarchar(100) NULL;
UPDATE dbo.[Order] SET ShippedBy = N'' WHERE ShippedBy IS NULL;
ALTER TABLE dbo.[Order] ALTER COLUMN ShippedBy nvarchar(100) NOT NULL;
If the column has a default, the type change also needs the default removed first and put back afterwards. SQL Server refuses to change the type of a column a default is attached to. See why DROP COLUMN fails with "The object 'DF__…' is dependent on column" for how to find and drop it.
On a large table
Tightening a column reads every row, and filling a lot of NULLs writes them. Do the UPDATE in
batches so the transaction log and the locks stay manageable:
WHILE 1 = 1
BEGIN
UPDATE TOP (10000) dbo.[Order]
SET ShippedBy = N''
WHERE ShippedBy IS NULL;
IF @@ROWCOUNT = 0 BREAK;
END
Then run the ALTER at a quiet time. It takes a schema modification lock on the table for as long
as the check takes.
Letting the script do it
Untick Null on a column in WoodFireERD and the migration script fills the empty rows before it tightens the column. It uses the column's default if it has one, and otherwise blank text, zero, the current date and time, or a new GUID for each row. If the type changes in the same edit, the script changes the type first, then fills, then tightens. The fill appears as its own step in Pending changes, with the value it writes.