CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Tanu
Tanu

Reputation: 5

Changing the default value in SQL Server 2012

I need to update the existing default value for one of the columns in a table.

table name: TRANSACTIONS

column name :CURRENCY current default

value: not available new current default value : NA

Can someone please let me know how I can update the existing default value to the new default value.

I tried using the below statments but they do not work for me:

ALTER TABLE TRANSACTIONS ALTER COLUMN CURRENCY SET DEFAULT 'NA';

ALTER TABLE TRANSACTIONS ADD CURRENCY varchar(256) DEFAULT 'NA';

ALTER TABLE TRANSACTIONS MODIFY COLUMN CURRENCY VARCHAR(256) NOT NULL DEFAULT 'NA';

ALTER TABLE TRANSACTIONS CHANGE COLUMN CURRENCY CURRENCY VARCHAR(256) NOT NULL DEFAULT 'NA';

alter table TRANSACTIONS alter column CURRENCY set default 'NA';

Upvotes: 0

Views: 1064

Answers (1)

Matthew
Matthew

Reputation: 462

Drop the default value constraint then recreate:

ALTER TABLE TRANSACTIONS DROP CONSTRAINT DF_TRANSACTIONS_CURRENCY
GO
ALTER TABLE TRANSACTIONS ADD CONSTRAINT DF_TRANSACTIONS_CURRENCY DEFAULT ('NA') FOR CURRENCY
GO

You may have to change "DF_TRANSACTIONS_CURRENCY" to the name of your current constraint if it's different.

Upvotes: 1

Related Questions