使用外键约束的情况下删除Mysql表格列

Question

如何在MySQL中删除具有所有相关外键和其他约束的列？

- Ruchi

Answer 1

使用SET FOREIGN_KEY_CHECKS = 0;，然后修改包含约束定义的表。完成后，将FOREIGN_KEY_CHECKS恢复为1。

- Michael J.V.

Answer 2

据我所知，在MySQL中，在删除列之前必须手动删除约束。如果你使用ON DELETE CASCADE子句创建了该约束，则只有在需要删除与该表具有该约束的外键行时才会影响该表。请注意保留HTML标签。

- VAShhh

Answer 3

使用 ON DELETE CASCADE

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

- Otto

Answer 4

如果你正在使用Java:

public void deleteColumn(Connection connection, String tableName, String columnName) throws SQLException {
    // Drop all constraints that contain the specified column
    {
        final ResultSet resultSet = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY).executeQuery("select CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA = SCHEMA() and TABLE_NAME = '" + tableName + "' and COLUMN_NAME = '" + columnName + "'");
        while (resultSet.next()) {
            final String constraintName = resultSet.getString("CONSTRAINT_NAME");
            connection.createStatement().executeUpdate("ALTER TABLE `" + tableName + "` DROP FOREIGN KEY`" + constraintName + "`"); // Drop the foreign key constraint          
        }
        resultSet.close();
    }

    // Drop all indexes that contain the specified column:
    {
        final ResultSet resultSet = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY).executeQuery("SHOW INDEX FROM `" + tableName + "` where column_name = '" + columnName+ "'");
        while (resultSet.next()) {
            final String keyName = resultSet.getString("Key_name");
            connection.createStatement().executeUpdate("ALTER TABLE `" + tableName + "` DROP INDEX `" + keyName + "`"); // Drop the index               
        }
        resultSet.close();
    }

    // Drop the column:
    connection.createStatement().executeUpdate("ALTER TABLE `" + tableName + "` DROP COLUMN `" + columnName + "`"); // Drop the column
}

- Squ1sh

---

你真是救星！谢谢！这正是我要找的，这样我就可以删除一个带有ForeignKey的列了。 - marcolopes