[2.0.1] MSSQL dropColumn with default value set

Liquibase Development
1

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?

2

Ran into the problem an hour after the above response :slight_smile:

http://msdn.microsoft.com/en-us/library/ms188747.aspx shows syntax like:

Nathan

3

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 unnecessary 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. 


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 


4

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 :slight_smile: