Removing a default value from a Sql Server 2005 table

Question

Hi,

I have a tabel in an Sql Server 2005 database. I have the following column

IndPL INT DEFAULT 0 NULL

I want to change the column to be of the type NVARCHAR but I receive a constraint violation due to the fact that the column has a default value constraint attached to it.

I need to find out how to remove a default value constraint from a table column or how to change the column type without impeding the constraint.

Added :

I need to do it by using T-SQL in a script that will later be executed on an another machine.

Answer 1

You need to remove the constraint first. In Management Studio open the table, open the Contraints and click on the contraint in question. Delete it. Change the data-type and then add any necessary constraints.

Script:

ALTER TABLE YourTable 

DROP CONSTRAINT Your_Contraint

ALTER TABLE YourTable 

ALTER COLUMN IndPL NVARCHAR(150)