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
?
alter table
. Curious why SSMS thinks it has to alter all the other tables, when the referenced column is not changing! – Andomar May 21 '15 at 9:56ALTER TABLE
safe enough for this particular usage? – Álvaro González May 21 '15 at 10:53alter
is very safe, not sure why you'd doubt that – Andomar May 21 '15 at 11:51