Framework: Rollback migration with SQL Server Failing

[uxweb]

Hi, i am using laravel with SQL Server to develop my apps.

Recently i had to create a migration to add some fields to an existing table.

This fields are boolean and have a default value of false.

here is the code of my migration file:

public function up()
    {
        Schema::table('Subcontratos.datos_adicionales', function(Blueprint $table)
        {
            $table->boolean('es_requerida_fianza_anticipo')->default(false);
            $table->boolean('es_requerida_fianza_cumplimiento')->default(false);
            $table->boolean('es_requerida_fianza_vicios_ocultos')->default(false);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('Subcontratos.datos_adicionales', function(Blueprint $table)
        {
            $table->dropColumn([
                'es_requerida_fianza_anticipo',
                'es_requerida_fianza_cumplimiento',
                'es_requerida_fianza_vicios_ocultos',
            ]);
        });
    }

When i run this migration, it adds the fields to the table and for the default values, it creates one DEFAULT CONSTRAINT for each field with this names:

DF__datos_adi_es_re__3A827E7E
DF__datos_adi_es_re__3B76A2B7
DF__datos_adi_es_re__3C6AC6F0

I know that SQL Server creates these default names because of the kind of statement used to create/add the fields to the table, which is this:

ALTER TABLE Subcontratos.datos_adicionales
ADD es_requerida_fianza_anticipo BIT NOT NULL DEFAULT(0);

This statement makes SQL Server to create a DEFAULT constraint with a random name as above.

Now, the problem is that when i try to rollback this migration, it fails because each of the constraints depends on each field, so the constraints must be dropped first and then the fields.

I know that because i tried to execute the drop statement directly in the database and got this error:

Msg 5074, Level 16, State 1, Line: 1.
The object 'DF__datos_adi__es_re__3C6AC6F0' is dependent on column 'es_requerida_fianza_vicios_ocultos'.
Msg 4922, Level 16, State 9, Line: 1.
ALTER TABLE DROP COLUMN es_requerida_fianza_vicios_ocultos failed because one or more objects access this column.

So i have to delete these constraints manually before i can rollback this migration, which is horrible!! :(

I think that something must be changed in the IlluminateDatabaseSchema\GrammarsSqlServerGrammar to let assign a name to the constraints.

In SQL Server there are some constraint types:

DEFAULT
CHECK
FOREIGN KEY
UNIQUE

A better way to create this constraint is by using this statement:

-- FOR A DEFAULT CONSTRAINT
ALTER TABLE blabla
ADD CONSTRAINT DF_constraint_name
DEFAULT (0);

-- FOR A UNIQUE CONSTRAINT
ALTER TABLE blabla
ADD CONSTRAINT UQ_contraint_name
UNIQUE(unique_field);

Assigning a known name to the constraints will let me to drop them before drop the fields.

I am not sure if a second parameter to the $table->default() method can be added to let specify the name of the constraint to be created.

Then in the down() method of the migration i can do something like:

DB::statement('ALTER TABLE blabla DROP CONSTRAINT DF_constraint_name');
$table->dropColumn('column_to_be_dropped');

Or maybe add a new method to create and drop constraints?

I would like to know your opinion on this.

Thank you very much!

[rkgrep]

$table->dropUnique(..); or $table->dropForeign(...) should do the trick.
https://github.com/laravel/framework/blob/5.1/src/Illuminate/Database/Schema/Grammars/SqlServerGrammar.php#L214

[uxweb]

@rkgrep I know those methods, the case is that Laravel gives no chance to set the name of a default constraint, then, i can not use dropUnique because i don't know the name of the constraint.

This should not be closed!!

[rkgrep]

http://www.w3schools.com/sql/sql_default.asp
This sais that ALTER COLUMN City DROP DEFAULT is possible so you might be able to drop constraint without its name. However there is really no method in grammar to do this.
Unfortunately I have no ability to test SQL Server queries at the moment.

If this issue will be reopened then someone will probably implement the fix

[sherwinmartin]

i'm running into this as well. i tried
$table->dropUnique()
$table->dropUnique('DEFAULT')

it doesn't work :(

from my test, it's only affecting newest migration file. i have older tables created with migration and as soon as i delete the constraints in the latest migration, that table is rollback, plus the older tables that have constraints aren't even affected.

after more testing, Schema::drop('table'); runs fine but $table->dropColumn() is the one causing the error.

[funkflute]

I'm having this issue as well, please reopen.

[sherwinmartin]

what i ended up doing is not use default in the migration. i handle it on the controller during validation.

[mdurys]

I circumvent this issue by using DB::statement() to specify constraint name myself:

    public function up()
    {
        DB::statement('ALTER TABLE "my_table" ADD "my_column" BIT NOT NULL CONSTRAINT "DF_my_table_my_column" DEFAULT(0)');
    }

    public function down()
    {
        DB::statement('ALTER TABLE "my_table" DROP CONSTRAINT "DF_my_table_my_column", COLUMN "my_column"');
    }