0

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';
Community
  • 1
  • 1
Tanu
  • 5
  • 4
  • 1
    Seems like a bad choice of default value. What's wrong with NULL? – jarlh Feb 13 '17 at 09:07
  • 3
    Possible duplicate of [Modify Default value in SQL Server](http://stackoverflow.com/questions/15547210/modify-default-value-in-sql-server) – Heinzi Feb 13 '17 at 09:07
  • Defaults are constraints. Possible duplicate of [altering DEFAULT constraint on column SQL](http://stackoverflow.com/questions/20363386/altering-default-constraint-on-column-sql) – Cee McSharpface Feb 13 '17 at 09:07
  • DEFAULT is a CONSTRAINT, drop it by name and recreate again – McNets Feb 13 '17 at 09:10
  • If my answer helped, then please mark as the answer. If it didn't, then let me know why and I'll try to help some more! ;-) – Matthew Feb 17 '17 at 09:06

1 Answers1

1

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.

Matthew
  • 462
  • 8
  • 20