Alter the column default value for an existing column in SQL Server 2012

Question

I have an existing column (ROLE) whose default value needs to be updated from 'zzzz' to 'S'.

I tried using the below query, which obviously doesn't work:

ALTER TABLE [TRANSACTIONS] ALTER COLUMN [ROLE] ADD DEFAULT('S') ;

Can someone please help understand if it is even possible to update the column default value and if yes, then how?

Thanks

Answer 1

You need to drop and re-add the default constraint to specify a different value.

ALTER TABLE dbo.TRANSACTIONS DROP CONSTRAINT DF_TRANSACTIONS_ROLE;
ALTER TABLE dbo.TRANSACTIONS ADD CONSTRAINT DF_TRANSACTIONS_ROLE DEFAULT ('S') FOR [ROLE];

However, since you didn't explicitly name the constraint (which is a best practice), you'll need to determine the existing constraint name for the DROP statement:

SELECT name
FROM sys.default_constraints AS dc
WHERE
    dc.parent_object_id = OBJECT_ID(N'dbo.TRANSACTIONS')
    AND dc.parent_column_id = COLUMNPROPERTYEX(dc.parent_object_id, 'ROLE', 'ColumnID');

Answer 2

You need to look up the name of the current default constraint. Suppose it is named DefaultConstraintName, then you would execute this query:

ALTER TABLE TRANSACTIONS DROP CONSTRAINT DefaultConstraintName;
GO
ALTER TABLE TRANSACTIONS ADD CONSTRAINT DF_TRANSACTIONS_ROLE DEFAULT ('S') FOR ROLE;
GO

Since you showed us the code you would use to create a constraint, your existing constraint name is probably generated by SQL Server having a part of a GUID in it's name. This is not good practice and it's always good to name your constraints as I have in the above code example.