Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alter Column - Add Not Null Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2006 4:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 5:23 AM
Points: 12, Visits: 16

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
Post #256310
Posted Tuesday, February 7, 2006 5:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 8:02 AM
Points: 302, Visits: 294

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




Post #256328
Posted Tuesday, February 7, 2006 6:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

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




Post #256335
Posted Thursday, February 9, 2006 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 5:23 AM
Points: 12, Visits: 16

Thanks Kenneth.

I hope there is no other way to set the default and not null together in one command.

Deepa




Deepa
Post #256968
Posted Thursday, February 9, 2006 3:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672

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




Post #256976
Posted Friday, March 3, 2006 8:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 5:54 PM
Points: 99, Visits: 22
ALTER TABLE [dbo].[TableName] ADD [ColumnName] bit NOT NULL
CONSTRAINT Default_ColumnName DEFAULT 0
GO
Post #263121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse