Sql-server – Code required to make column not nullable

sql serversql-server-2008ssmst-sql

I have a table where I mistakenly allowed NULL values in a column:

CREATE TABLE EXAMPLE (
    EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL,
    FOO_ID INT,
    CREATED_ON DATETIME DEFAULT CURRENT_TIMESTAMP, -- Oops!
    CODE VARCHAR(50),
    QUOTE DECIMAL(18,2),
    BAR_ID TINYINT NOT NULL DEFAULT 1,

    CONSTRAINT EXAMPLE_PK PRIMARY KEY (EXAMPLE_ID),

    CONSTRAINT EXAMPLE_FK1 FOREIGN KEY (FOO_ID)
    REFERENCES FOO (FOO_ID)
    ON DELETE CASCADE,

    CONSTRAINT EXAMPLE_FK2 FOREIGN KEY (BAR_ID)
    REFERENCES BAR (BAR_ID)
);

CREATE INDEX EXAMPLE_IDX1 ON EXAMPLE (FOO_ID);
CREATE INDEX EXAMPLE_IDX2 ON EXAMPLE (BAR_ID);

It's a fairly unimportant column (not indexed, not foreign key, not used in triggers…) and this is how I'd normally fix it:

ALTER TABLE EXAMPLE
ALTER COLUMN CREATED_ON DATETIME NOT NULL;
-- Runs in 1 second

However, I decided to use a GUI tool (SQL Server Management Studio, but all other tools I've tried do a similar thing) and found the whole process took like 20 minutes. The reason? The automatically generated change code is a 3351-line script that apparently tries to edit every single table on database that has a foreign key pointing to EXAMPLE.EXAMPLE_ID!

ALTER TABLE dbo.SOME_OTHER_TABLE
DROP CONSTRAINT SOME_OTHER_TABLE_FK
GO


BEGIN TRANSACTION
GO
ALTER TABLE dbo.SOME_OTHER_TABLE ADD CONSTRAINT
    SOME_OTHER_TABLE_FK FOREIGN KEY
    (
        EXAMPLE_ID
    ) REFERENCES dbo.EXAMPLE
    (
        EXAMPLE_ID
    ) ON UPDATE  NO ACTION
    ON DELETE  CASCADE

GO
ALTER TABLE dbo.SOME_OTHER_TABLE SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Further digging reveals that the script creates a new Tmp_EXAMPLE table with the final definition and copies all data from EXAMPLE.

Is this complex process necessary? What's the exact problem with plain ALTER TABLE?

Best Answer

Just take care of current NULL values by updating them to a default value.
Then do your ALTER TABLE

Example for an integer

UPDATE [MyTable]
SET [MyColumn] = 0
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] INTEGER NOT NULL;

Example for a varchar

UPDATE [MyTable]
SET [MyColumn] = ''
WHERE [MyColumn] IS NULL;
ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] VARCHAR(max) NOT NULL;

Altering a column: null to not null

Security Measure

Before, make sure you backup your database in case of you screw it all

To make short

This whole complex scenario made by SSMS is not necessary there is no problem doing a plain ALTER TABLE

Related Question