# \[2.0.1\] MSSQL dropColumn with default value set

**URL:** <https://forum.liquibase.org/t/2-0-1-mssql-dropcolumn-with-default-value-set/2522>\
**Category:** Liquibase Development\
**Created:** [December 14, 2011, 12:34am UTC](https://forum.liquibase.org/t/2-0-1-mssql-dropcolumn-with-default-value-set/2522 "2011-12-14T00:34:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![nessumo](https://avatars.discourse-cdn.com/v4/letter/n/a87d85/32.png) [@nessumo](https://forum.liquibase.org/u/nessumo)\
**Post date:** [December 14, 2011, 12:34am UTC](https://forum.liquibase.org/t/2-0-1-mssql-dropcolumn-with-default-value-set/2522/1 "2011-12-14T00:34:00Z")

</div>

MSSQL is fat & ugly and cannot drop column if default value is set. I’ve tried to fix this by creating custom DropColumnChange implementation which generates additional DropDefaultValueStatement, but this fails for another reason: dropping non-existing constraint causes crash. So…

a) this is not an issue, you _must_ always directly drop default value (stupid?)

b) this is an issue, should be solved this way, and dropDefaultValues should be a NOP if no constraint present (easy?)

c) this is an issue and should be solved another way (most elegant?)

d) other (?)

What do you think about this?

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [December 14, 2011, 12:34am UTC](https://forum.liquibase.org/t/2-0-1-mssql-dropcolumn-with-default-value-set/2522/2 "2011-12-14T00:34:00Z")

</div>

Ran into the problem an hour after the above response 🙂  
  
[http://msdn.microsoft.com/en-us/library/ms188747.aspx](http://msdn.microsoft.com/en-us/library/ms188747.aspx)&nbsp;shows syntax like:

```
Nathan
```

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [December 14, 2011, 12:34am UTC](https://forum.liquibase.org/t/2-0-1-mssql-dropcolumn-with-default-value-set/2522/3 "2011-12-14T00:34:00Z")

</div>

I’ve ran into that problem as well. I generally have worked around it by adding the dropDefaultValue first. It is more explicit which has advantages, but does seem&nbsp;unnecessary&nbsp;and is easy to miss.

The best approach with a custom change is to find a way to write “drop default value for column X if it exists” in a sql statement. I’m not sure what sqlserver has for if/else logic that can be embedded in a non-stored proc and/or if can get the information joining on the info\_schema.&nbsp;

You could lookup the default value in the database metadata/snapshot before running the change, but that will not work in updateSql mode. I’m not sure if updateSql is a requirement for you or not.

I don’t know of any other ways to work around the issue off hand.

Nathan&nbsp;

---

<div class="post-metadata">

**Author:** ![nessumo](https://avatars.discourse-cdn.com/v4/letter/n/a87d85/32.png) [@nessumo](https://forum.liquibase.org/u/nessumo)\
**Post date:** [December 14, 2011, 12:34am UTC](https://forum.liquibase.org/t/2-0-1-mssql-dropcolumn-with-default-value-set/2522/4 "2011-12-14T00:34:00Z")

</div>

This is exactly my workaround for now. But this requires to rewrite SQLGenerator for dropColumn completely, so if you add something funny in next release I would cry loud 🙂