Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

[teddysnips]

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'

DROP DEFAULT DF_tblIndividual_fldRenewalStatus

ALTER TABLE tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default

ALTER TABLE tblIndividual
DROP COLUMN fldRenewalStatus
GO

ALTER TABLE tblIndividual
ADD fldRenewalStatus int NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0
Thoughts?

Thanks

Edward

[Roy Harvey]

ALTER the table to add a new INT column. Set the value of that column
based on the data in the BIT column. Alter the table to drop the BIT
column. Rename the INT column.

Roy Harvey
Beacon Falls, CT

On 2 Jan 2007 09:19:00 -0800, te********@hotmail.com wrote:

>A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

EXEC sp_unbindefault 'tblIndividual.fldRenewalStatus'

DROP DEFAULT DF_tblIndividual_fldRenewalStatus

ALTER TABLE tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default

ALTER TABLE tblIndividual
DROP COLUMN fldRenewalStatus
GO

ALTER TABLE tblIndividual
ADD fldRenewalStatus int NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0
Thoughts?

Thanks

Edward

[Hugo Kornelis]

On 2 Jan 2007 09:19:00 -0800, te********@hotmail.com wrote:

>A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

Hi Edward,

You can do as Roy suggests, or you can run the following script:

ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;

ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;

ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;

Running the ALTER COLUMN might take long if you have lots of data!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Roy Harvey]

On Wed, 03 Jan 2007 00:06:18 +0100, Hugo Kornelis
<hu**@perFact.REMOVETHIS.info.INVALIDwrote:

>You can do as Roy suggests, or you can run the following script:

Much simpler, of course. Somehow I had the idea that bit would not
convert to int. Thanks for the diplomatic correction. 8-)

Roy

[teddysnips]

Hugo Kornelis wrote:

On 2 Jan 2007 09:19:00 -0800, te********@hotmail.com wrote:

A few weeks ago a client asked me to add a column to a table so I
created this script:

ALTER TABLE dbo.tblIndividual ADD fldRenewalStatus BIT NOT NULL
CONSTRAINT fldRenewalStatus_Default DEFAULT 0

Now they want to change it from a BIT to an INT, to store an enum.
Fair enough. However, no matter how much I wrangle with a script, I
can't find a reliable way to alter the column. I've mixed and matched
the following and nothing seems to work:

Hi Edward,

You can do as Roy suggests, or you can run the following script:

ALTER TABLE dbo.tblIndividual
DROP CONSTRAINT fldRenewalStatus_Default;

ALTER TABLE dbo.tblIndividual
ALTER COLUMN fldRenewalStatus INT NOT NULL;

ALTER TABLE dbo.tblIndividual
ADD CONSTRAINT fldRenewalStatus_Default DEFAULT 0 FOR fldRenewalStatus;

Running the ALTER COLUMN might take long if you have lots of data!

Thanks Hugo - that worked a treat!

Edward