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

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

EdwardALTER 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, teddysnips@.hotmail.com wrote:

Quote:

Originally Posted by

>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

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

Quote:

Originally Posted by

>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|||On Wed, 03 Jan 2007 00:06:18 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>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|||Hugo Kornelis wrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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