Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-255

On SQL SERVER Trying to drop a column throws error because of auto generated Constraints

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: None
    • Component/s: Platforms
    • Security Level: All
    • Labels:
      None
    • Environment:
      MS SQL Server + Windows Server 2008

      Description

      Whenever Doctrine tries to drop a column that has some implicit constraints the system the SQL Native Client throws the message:

      The Object 'Name_Of_The_Object' is dependent on column 'Column_To_Drop';

      This is probably because columns such as Decimal Numbers have an automatice generated constraint that need to be dropped in order to drop a column.

      A possible Solution is to add a platform specific getConstratintForTableSQL that queries the database to get all the constraint for the given column, than override the 'alterTable' method in the SQLServer Schema Manager to first drop the constraint than the columns in question by going and checking all the Columns that needs to be dropped in the TableDiff passed.

      Something similar to this but better refactored I guess:

      Changes in SQLServerPlatform.php

      Add a method similar to this:

          /**
           * This function retrieves the constraints for a given column that is going to be droppped 
           */
          public function getColumnConstraintSQL($table, $column)
          {
              return "SELECT SysObjects.[Name]
                      From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
                      On Tab.[ID] = Sysobjects.[Parent_Obj] 
                      Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
                      Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
                      Where Col.[Name] = '$column' and Tab.[Name] = '$table'
                      order by Col.[Name]";
          }
      

      Changes on SqlServerChemaManager.php

             /**
      	* Override
      	*/
      	public function alterTable(TableDiff $tableDiff)
      	{
      		if(count($tableDiff->removedColumns) > 0){
      			$constraintsSql = array();
      			foreach($tableDiff->removedColumns as $col){
      				$constraintsSql[] = $this->_platform->getColumnConstraintSQL($tableDiff->name, $col->oldColumnName);
      			}
      			$constraintsToDrop = array();
      			foreach($constraintsSql as $sql){
      				 $constraintsToDrop[] = $this->_conn->execute($sql);
      			}
      			foreach($constraintsToDrop as $constraint){
      				$this->_conn->execute("ALTER TABLE $tableDiff->name DROP CONSTRAINT $constraint");
      			}
      		}
      		return parent::alterTable($tableDiff);
      	}
      

      Hope this helps

      Thx

        Activity

        Fryderyk Benigni created issue -
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Description Whenever Doctrine tries to drop a column that has some implicit constraints the system the SQL Native Client throws the message:

        The Object 'Name_Of_The_Object' is dependent on column 'Column_To_Drop';

        This is probably because columns such as Decimal Numbers have an automatice generated constraint that need to be dropped in order to drop a column.

        A possible Solution is to add a platform specific getConstratintForTableSQL that queries the database to get all the constraint for the given column, than override the 'alterTable' method in the SQLServer Schema Manager to first drop the constraint than the columns in question by going and checking all the Columns that needs to be dropped in the TableDiff passed.

        Something similar to this but better refactored I guess:

        Changes in SQLServerPlatform.php

        Add a method similar to this:

        /**
             * This function retrieves the constraints for a given column that is going to be droppped
             */
            public function getColumnConstraintSQL($table, $column)
            {
                return "SELECT SysObjects.[Name]
                        From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
                        On Tab.[ID] = Sysobjects.[Parent_Obj]
                        Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]
                        Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
                        Where Col.[Name] = '$column' and Tab.[Name] = '$table'
                        order by Col.[Name]";
            }

        Changes on SqlServerChemaManager.php


        /**
        * Override
        */
        public function alterTable(TableDiff $tableDiff)
        {
        if(count($tableDiff->removedColumns) > 0){
        $constraintsSql = array();
        foreach($tableDiff->removedColumns as $col){
        $constraintsSql[] = $this->_platform->getColumnConstraintSQL($tableDiff->name, $col->oldColumnName);
        }
        $constraintsToDrop = array();
        foreach($constraintsSql as $sql){
        $constraintsToDrop[] = $this->_conn->execute($sql);
        }
        foreach($constraintsToDrop as $constraint){
        $this->_conn->execute("ALTER TABLE $tableDiff->name DROP CONSTRAINT $constraint");
        }
        }
        return parent::alterTable($tableDiff);
        }

        Hope this helps

        Thx
        Whenever Doctrine tries to drop a column that has some implicit constraints the system the SQL Native Client throws the message:

        The Object 'Name_Of_The_Object' is dependent on column 'Column_To_Drop';

        This is probably because columns such as Decimal Numbers have an automatice generated constraint that need to be dropped in order to drop a column.

        A possible Solution is to add a platform specific getConstratintForTableSQL that queries the database to get all the constraint for the given column, than override the 'alterTable' method in the SQLServer Schema Manager to first drop the constraint than the columns in question by going and checking all the Columns that needs to be dropped in the TableDiff passed.

        Something similar to this but better refactored I guess:

        Changes in SQLServerPlatform.php

        Add a method similar to this:

        {code}
            /**
             * This function retrieves the constraints for a given column that is going to be droppped
             */
            public function getColumnConstraintSQL($table, $column)
            {
                return "SELECT SysObjects.[Name]
                        From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab
                        On Tab.[ID] = Sysobjects.[Parent_Obj]
                        Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]
                        Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
                        Where Col.[Name] = '$column' and Tab.[Name] = '$table'
                        order by Col.[Name]";
            }
        {code}

        Changes on SqlServerChemaManager.php

        {code}
               /**
        * Override
        */
        public function alterTable(TableDiff $tableDiff)
        {
        if(count($tableDiff->removedColumns) > 0){
        $constraintsSql = array();
        foreach($tableDiff->removedColumns as $col){
        $constraintsSql[] = $this->_platform->getColumnConstraintSQL($tableDiff->name, $col->oldColumnName);
        }
        $constraintsToDrop = array();
        foreach($constraintsSql as $sql){
        $constraintsToDrop[] = $this->_conn->execute($sql);
        }
        foreach($constraintsToDrop as $constraint){
        $this->_conn->execute("ALTER TABLE $tableDiff->name DROP CONSTRAINT $constraint");
        }
        }
        return parent::alterTable($tableDiff);
        }
        {code}

        Hope this helps

        Thx
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.2.3 [ 10210 ]
        Resolution Fixed [ 1 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 13628 ] jira-feedback2 [ 17815 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 17815 ] jira-feedback3 [ 20170 ]
        Guilherme Blanco made changes -
        Fix Version/s 2.2.3 [ 10210 ]

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Fryderyk Benigni
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: