How to delete (drop) a column from a database table?

dmitry_fedyuk · January 26, 2016, 4:39pm

Use the dropColumn() method of the \Magento\Framework\DB\Adapter\AdapterInterface:

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/AdapterInterface.php#L297-L305


/**
 * Drop the column from table
 *
 * @param string $tableName
 * @param string $columnName
 * @param string $schemaName
 * @return boolean
 */
public function dropColumn($tableName, $columnName, $schemaName = null);

A usage example from the core:

github.com

magento/magento2/blob/720667e/app/code/Magento/Catalog/Setup/UpgradeSchema.php#L175-L178


/**
 * Drop entity Id columns
 */
$setup->getConnection()->dropColumn($setup->getTable(Gallery::GALLERY_TABLE), 'entity_id');

By default the \Magento\Framework\DB\Adapter\AdapterInterface interface is implemented by the \Magento\Framework\DB\Adapter\Pdo\Mysql class:

github.com

magento/magento2/blob/720667e/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L886-L932


/**
 * Delete table column
 *
 * @param string $tableName
 * @param string $columnName
 * @param string $schemaName
 * @return true|\Zend_Db_Statement_Pdo
 */
public function dropColumn($tableName, $columnName, $schemaName = null)
{
    if (!$this->tableColumnExists($tableName, $columnName, $schemaName)) {
        return true;
    }


    $alterDrop = [];


    $foreignKeys = $this->getForeignKeys($tableName, $schemaName);
    foreach ($foreignKeys as $fkProp) {
        if ($fkProp['COLUMN_NAME'] == $columnName) {
            $alterDrop[] = 'DROP FOREIGN KEY ' . $this->quoteIdentifier($fkProp['FK_NAME']);
        }
    }


    /* drop index that after column removal would coincide with the existing index by indexed columns */
    foreach ($this->getIndexList($tableName, $schemaName) as $idxData) {
        $idxColumns = $idxData['COLUMNS_LIST'];
        $idxColumnKey = array_search($columnName, $idxColumns);
        if ($idxColumnKey !== false) {
            unset($idxColumns[$idxColumnKey]);
            if ($idxColumns && $this->_getIndexByColumns($tableName, $idxColumns, $schemaName)) {
                $this->dropIndex($tableName, $idxData['KEY_NAME'], $schemaName);
            }
        }
    }


    $alterDrop[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName);
    $sql = sprintf(
        'ALTER TABLE %s %s',
        $this->quoteIdentifier($this->_getTableName($tableName, $schemaName)),
        implode(', ', $alterDrop)
    );


    $result = $this->rawQuery($sql);
    $this->resetDdlCache($tableName, $schemaName);


    return $result;
}

dmitry_fedyuk · January 26, 2016, 4:42pm

mage2pro/core/blob/1.8.13/Core/lib/db-column.php?ts=4#L29-L53


/**
* 2016-11-04
* The function does nothing if the $column column is absent in the $table.
* @param string $table
* @param string $column
* @return void
*/
function df_db_column_drop($table, $column) {
	/**
	 * 2016-11-04
	 * @uses df_table() call is required here,
	 * because @uses \Magento\Framework\DB\Adapter\Pdo\Mysql::dropColumn() method
	 * does not add the custom table prefix to the $name.
	 * The custom table prefix could be set my a Magento 2 administrator
	 * during Magento 2 intallation (see the «table_prefix» key in the app/etc/env.php file).
	 */
	df_conn()->dropColumn(df_table($table), $column);
	/**
	 * 2016-11-04
	 * @see \Magento\Framework\DB\Adapter\Pdo\Mysql::resetDdlCache() call is not needed here,
	 * because it has already been called
	 * from @uses \Magento\Framework\DB\Adapter\Pdo\Mysql::dropColumn()
	 * https://github.com/magento/magento2/blob/2.1.2/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L938
	 */
}