Geek asks and answers

Mysql Column Table Column with Foreign Key Constraint

How to delete a column with all associated foreign key and other constraint in mysql?

+3
source share
4 answers

AFAIK on MySQL, you need to manually remove the constraints before deleting the column.

The ON DELETE CASCADE clause will affect a table only if you create a constraint with this clause, and this is only necessary to delete the outer rows associated with this table with this constraint.

+2
source

Use SET FOREIGN_KEY_CHECKS = 0;, and then modify the table containing the constraint definition. When finished, turn FOREIGN_KEY_CHECKSback 1.

+4
source

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
}
+1

More articles:


All Articles