如何在MySQL中删除具有所有相关外键和其他约束的列?
使用SET FOREIGN_KEY_CHECKS = 0;
,然后修改包含约束定义的表。完成后,将FOREIGN_KEY_CHECKS
恢复为1。
使用 ON DELETE CASCADE
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
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
}