I need to make a existing Nullable column in a table to a Not Null and also add a default constraint to the column to store value "0" (zero).
I've tried the following command but it comes with error.
ALTER TABLE DATAALTER COLUMN [IsTested]SET DEFAULT 0 WITH VALUESGO
ALTER TABLE DATAALTER COLUMN [IsTested] [bit] NOT NULL GO
Please let me know if you have any solution
To Add a default constraint of 0 and then change the filed to NOT NULL I would use the following:
ALTER TABLE DATA ADD CONSTRAINT DF_DATA_istested DEFAULT 0 FOR istestedGO
ALTER TABLE DATAALTER COLUMN [IsTested] [bit] NULL GO
To create a new default and also seed with the WITH VALUES clause only works when adding a new column, not when changing an existing column.
What you need to do is three things.
Add the defaultUpdate the column to the default value where it is null.Alter the column to not null
The first two steps can be done in any order.
ALTER TABLE DATAadd constraint DF_IsTested DEFAULT(0) FOR IsTested GO
UPDATE DATASET IsTested = 0 WHERE IsTested IS NULLGO
I hope there is no other way to set the default and not null together in one command.
As far as I've been able to find out, when changing an existing column - No. When adding a new column - Yes.
Though, what difference does it make anyway?Whenever we want to perform something that is made up of more than one distinct operation, but we want to handle all the different steps as one single command, we have the opportunity to do those within a transaction.
Thus, even though you 1) add a default2) update all null to the default value3) change column null to not null
..by enclosing all three within a BEGIN TRANSACTION and COMMIT TRANSACTION block, they all become one logical command anyway.
..or is there some other compelling reason for you wanting the 'single command' so bad?