Posted by teddysnips on 01/02/07 17:19

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

• Next in forum: Re: many tables vs one big table
• Prev in forum: many tables vs one big table
• Thread view: Changing a BIT to an INT where there's a CONSTRAINT and a DEFAULT

[Reply to this message]