Modify column with a constraint on SQLS errors

[Daemach]

The following code gives the following error. It used to be a tinyint with a default constraint.

    schema.alter( "mars_in_wash_sales", function( table ) {
      table.modifyColumn( "shares", table.smallinteger( "shares").default(-999) );
  } ); 

ERROR (5.2.1-alpha+00282)

Incorrect syntax near the keyword 'CONSTRAINT'.

C:\Users\johnw.CommandBox\cfml\modules\commandbox-migrations\modules\cfmigrations\modules\qb\models\Schema\SchemaBuilder.cfc ALTER TABLE [mars_in_wash_sales] ALTER COLUMN [shares] SMALLINT NOT NULL CONSTRAINT [df_mars_in_wash_sales_shares] DEFAULT -999

[Daemach]

I tried a number of things but this is the only way to make it work.

    schema.alter( "mars_in_wash_sales", function( table ) {
      table.dropConstraint( table.default( "shares" ) );
      table.dropColumn( "shares" );
      table.addColumn( table.smallinteger( "shares" ).default(-999) );
    } ); 

[elpete]

Did this column have a default value before? Or was this the first time it was being added?

[Daemach]

It did. .default(0) created with schemabuilder.

[elpete]

This is quite complicated, actually. You would need to automatically drop any constraints related to the column. If the column never had a constraint, you don't need to drop anything but also trying to drop a constraint based on a convention would fail. I think the approach I'm going to take for now is to document in modifyColumn that you want to manually drop any constraints as well.

That being said, I'm not opposed to an automatic dropping of constraints for modifyColumn calls. I just won't be able to fit it in 8.5.0 myself.

[Daemach]

It looks like "drop constraint if exists" should work: https://www.mssqltips.com/sqlservertip/4402/new-drop-if-exists-syntax-in-sql-server-2016/