Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-825

ALTER COLUMN on mssql is failing if default constraint is attached

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5
    • Component/s: Platforms
    • Labels:
      None
    • Environment:
      MSSQL

      Description

      Here is the unit test - implemented in class SchemaManagerFunctionalTestCase

       
          public function testChangeColumnsTypeWithDefault()
          {
              $table = new \Doctrine\DBAL\Schema\Table('column_change_type_test');
              $table->addColumn('id', 'integer', array('default' => 5));
      
              $this->_sm->createTable($table);
      
              $columns = $this->_sm->listTableColumns("column_change_type_test");
              $this->assertEquals(1, count($columns));
              $this->assertInstanceOf('Doctrine\DBAL\Types\IntegerType', $columns['id']->getType());
      
              $tableDiff = new \Doctrine\DBAL\Schema\TableDiff('column_change_type_test');
              $tableDiff->changedColumns['id'] = new \Doctrine\DBAL\Schema\ColumnDiff(
                  'id', new \Doctrine\DBAL\Schema\Column(
                      'id', \Doctrine\DBAL\Types\Type::getType('smallint'), array('default' => 5)
                  ),
                  array('type'),
                  new \Doctrine\DBAL\Schema\Column(
                      'id', \Doctrine\DBAL\Types\Type::getType('integer'), array('default' => '5')
                  )
              );
      
              $this->_sm->alterTable($tableDiff);
      
              $columns = $this->_sm->listTableColumns("column_change_type_test");
              $this->assertEquals(1, count($columns));
              $this->assertInstanceOf('Doctrine\DBAL\Types\SmallIntType', $columns['id']->getType());
              $this->assertSame('', $columns['id']->getDefault());
          }
      

      Causes following result

       
      Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL':
      
      SQLSTATE [42000, 5074]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF_A74995E2_BF396750' is dependent on column 'id'.
      SQLSTATE [42000, 4922]: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.
      
      With queries:
      5. SQL: 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL' Params: 
      4. SQL: 'SELECT    col.name,
                                type.name AS type,
                                col.max_length AS length,
                                ~col.is_nullable AS notnull,
                                def.definition AS [default],
                                col.scale,
                                col.precision,
                                col.is_identity AS autoincrement,
                                col.collation_name AS collation,
                                CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                      FROM      sys.columns AS col
                      JOIN      sys.types AS type
                      ON        col.user_type_id = type.user_type_id
                      JOIN      sys.objects AS obj
                      ON        col.object_id = obj.object_id
                      JOIN      sys.schemas AS scm
                      ON        obj.schema_id = scm.schema_id
                      LEFT JOIN sys.default_constraints def
                      ON        col.default_object_id = def.object_id
                      AND       col.object_id = def.parent_object_id
                      LEFT JOIN sys.extended_properties AS prop
                      ON        obj.object_id = prop.major_id
                      AND       col.column_id = prop.minor_id
                      AND       prop.name = 'MS_Description'
                      WHERE     obj.type = 'U'
                      AND       (obj.name = 'column_change_type_test' AND scm.name = SCHEMA_NAME())' Params: 
      3. SQL: 'ALTER TABLE column_change_type_test ADD CONSTRAINT DF_A74995E2_BF396750 DEFAULT 5 FOR id' Params: 
      2. SQL: 'CREATE TABLE column_change_type_test (id INT NOT NULL)' Params: 
      
      Trace:
      C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
      C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
      C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
      C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
      C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:621
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
      C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
      C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
      C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
      C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506
      
      #0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
      #1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
      #2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
      #3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
      #4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
      #5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
      #6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
      #7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
      #8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
      #9 {main}
      
      

        Issue Links

          Activity

          Show
          Thomas Müller added a comment - Possible solution: http://www.select-sql.com/mssql/how-to-alter-column-with-default-constraint-in-mssql.html
          Show
          Thomas Müller added a comment - Here is the unit test: https://github.com/DeepDiver1975/dbal/commit/53238301f7e124d31232e9b3eab774c32c9e04c4
          Hide
          Steve Müller added a comment -

          Thomas Müller Thanks for reporting. Which version of SQL Server is affected by this?

          Show
          Steve Müller added a comment - Thomas Müller Thanks for reporting. Which version of SQL Server is affected by this?
          Hide
          Thomas Müller added a comment -

          SQL Server 2012 Express Edition

          Show
          Thomas Müller added a comment - SQL Server 2012 Express Edition
          Show
          Steve Müller added a comment - Fixed in commit: https://github.com/doctrine/dbal/commit/171a8762673ee61a89e3d6ce891cd2b475e7b5f7

            People

            • Assignee:
              Steve Müller
              Reporter:
              Thomas Müller
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: