Yii2: Set default value in alterColumn causes MS SQL syntax error

[vitalcrazz]

What steps will reproduce the problem?

In a migration I have the following code:
$this->alterColumn('tableName', 'columnName', $this->boolean()->defaultValue(0));

What is the expected result?

I expected to change defaultValue of existing column in existing table.

What do you get instead?

It results in syntax error in MS SQL.

What do you propose

The right syntax for setting default value in MS SQL is:

ALTER TABLE tableName ADD CONSTRAINT constraintName DEFAULT 0 FOR columnName;

You cannot just use DEFAULT statement in ALTER COLUMN.

I suppose we need 2 more methods in yii\db\Migration class:
createDefault and dropDefault (like createIndex and dropIndex)

What do you think about this idea? If you agree I could try to make PR for this.

Additional info

| Q | A |
| --- | --- |
| Yii version | 2.0.7 |
| PHP version | 5.4 |
| Operating system | Windows 7 |

[SilverFire]

Might be related to #10921

[vitalcrazz]

Related to #9903

But in PostgreSQL (#9903) it is not a big difference between CREATE TABLE syntax and ALTER TABLE syntax (DEFAULT and SET DEFAULT).

In MS SQL the difference is huge. DEFAULT in CREATE TABLE syntax. And ADD CONSTRAINT in ALTER TABLE syntax.

[df2]

10921 has to be merged so this can work off of it. Then we should create a version of this as proposed by @vitalcrazz that conquers #9903 as well. #9903 should be covered by this.

[vitalcrazz]

I've done some research about default value creating and dropping in different DBMS.

| DBMS | Add default value | Drop default value |
| --- | --- | --- |
| MS SQL (specifying constraintName) | ALTER TABLE tableName ADD CONSTRAINT constraintName DEFAULT 0 FOR columnName; | ALTER TABLE tableName DROP CONSTRAINT constraintName; |
| MS SQL (without constraintName) | ALTER TABLE tableName ADD DEFAULT 0 FOR columnName; | Very complicated. stackoverflow |
| MySQL/PostgreSQL | ALTER TABLE tableName ALTER COLUMN columnName SET DEFAULT 0; | ALTER TABLE tableName ALTER COLUMN columnName DROP DEFAULT; |
| Oracle/Cubrid | ALTER TABLE tableName ALTER COLUMN columnName SET DEFAULT 0; | Not supported. Default value cannot be dropped. Only new default value can be set. |
| Sqlite | Not supported. Default value can be set only in ADD COLUMN and CREATE TABLE statements. | Not supported. |

Note: In MS SQL a new default value can be added only if an old default value was dropped.

[vitalcrazz]

I see 2 options here.

1) Explicitly specify constraintName ($name) in method signature. But it looks like a MSSQL oriented solution, because $name will be used only in MSSQL.

addDefaultValue($name, $table, $column, $value)
dropDefaultValue($name, $table, $column)

2) Refuse a constraintName. It looks much cleaner.

addDefaultValue($table, $column, $value)
dropDefaultValue($table, $column)

But in this case for MSSQL we have to use a little hack for dropping defaultValue.

DECLARE @ObjectName NVARCHAR(100);
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
IF @ObjectName IS NOT NULL
EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName);

http://stackoverflow.com/a/17303369
https://msdn.microsoft.com/en-us/library/ms176106(v=sql.120).aspx

I prefer the second option.

[sergeymakinen]

It basically means that DBMS QueryBuilders have to be altered to be able to create additional SQLs for different scenarios (add/alter/drop).
I'm unsure if an upcoming table migration builder should handle this or QueryBuilders but I tend to think that altering a default value / nullness should be resolved at a baseline. But is it okay to return an array of SQLs in query builders? I don't know yet. I think I'll raise another issue to discuss it.