Articles

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.

An unhandled error has occurred. Reload 🗙