February 7, 2006 at 4:33 am
Hi,
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 DATA
ALTER COLUMN [IsTested]
SET DEFAULT 0 WITH VALUES
GO
ALTER TABLE DATA
ALTER COLUMN [IsTested] [bit] NOT NULL
GO
Please let me know if you have any solution
Deepa
Deepa
February 7, 2006 at 5:51 am
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 istested
GO
ALTER TABLE DATA
ALTER COLUMN [IsTested] [bit] NULL
GO
February 7, 2006 at 6:11 am
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 default
Update 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 DATA
add constraint DF_IsTested DEFAULT(0) FOR IsTested
GO
UPDATE DATA
SET IsTested = 0
WHERE IsTested IS NULL
GO
ALTER TABLE DATA
ALTER COLUMN [IsTested] [bit] NOT NULL
GO
/Kenneth
February 9, 2006 at 2:29 am
Thanks Kenneth.
I hope there is no other way to set the default and not null together in one command.
Deepa
Deepa
February 9, 2006 at 3:04 am
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 default
2) update all null to the default value
3) 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?
/Kenneth
March 3, 2006 at 8:25 am
ALTER TABLE [dbo].[TableName] ADD [ColumnName] bit NOT NULL
CONSTRAINT Default_ColumnName DEFAULT 0
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply