Modify Default value in SQL Server

by

When you add a column with default value, what happens is that there’s a default constraint being created:

create table _temp 
( x int default 1)

sp_help _temp result:

constraint_type constraint_name
DEFAULT on column x DF___temp__x__5A3B20F9

So default clause in column definition is just a shortcut for creating a constraint; default value is not an intrinsic property of a column. If you want to modify default for already existing column, you have to drop the constraint first:

alter table _temp drop constraint DF___temp__x__5A3B20F9

And then create a new default constraint:

alter table _temp add constraint DF_temp_x default 2 for x

More Related Contents:

  1. Case statement for multiple columns
  2. SQL where clause odd behavior [closed]
  3. SQL Server Exec issue [closed]
  4. Count(*) vs Count(1) – SQL Server
  5. How do I escape a single quote in SQL Server?
  6. Only inserting a row if it’s not already there
  7. Unpivot with column name
  8. Selecting data from two different servers in SQL Server
  9. Is a view faster than a simple query?
  10. How to convert DateTime to VarChar
  11. Most efficient way in SQL Server to get date from date+time?
  12. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  13. SQL : BETWEEN vs =
  14. Stored Procedure with optional “WHERE” parameters
  15. SQL Server: Howto get foreign key reference from information_schema?
  16. SCOPE_IDENTITY() for GUIDs?
  17. Should I design a table with a primary key of varchar or int?
  18. How do you import a large MS SQL .sql file?
  19. How do I make a composite key with SQL Server Management Studio?
  20. Difference between Subquery and Correlated Subquery
  21. How to execute .sql file using powershell?
  22. Does Sql JOIN order affect performance?
  23. Best equivalent for IsInteger in SQL Server
  24. Syntax of for-loop in SQL Server
  25. Using tuples in SQL “IN” clause
  26. Why does ISNUMERIC(‘.’) return 1?
  27. Can SQL Server Express LocalDB be connected to remotely?
  28. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  29. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  30. Convert bigint to datetime

Leave a Comment