Posted by teddysnips on 01/03/07 08:44

Hugo Kornelis wrote:

> On 2 Jan 2007 09:19:00 -0800, teddysnips@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

• Next in forum: ssis issue
• Prev in forum: Re: design question
• Thread view: Re: Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

[Reply to this message]