How to delete a foreign key MySql

Question

How to delete a foreign key MySql

Navigation

#1 by (3 votes)

3

I'm having the same problem in all databases. I have a database created with the following lines

create database nova;
create table estado(
        id int primary key auto_increment, 
        nome varchar(20)
        );
create table pessoa(
        id int primary key auto_increment, 
        nome varchar(20), 
        estado int, 
        foreign key (estado) references estado(id)
        );

And I want to delete the state field in the person table, I get the following errors when executing the following lines:

alter table pessoa drop estado

1553 - Can not drop index 'StatusAutall': needed in a foreign key constraint

ALTER TABLE pessoa DROP INDEX estado;
ALTER TABLE pessoa DROP COLUMN estado

1553 - Can not drop index 'state': needed in a foreign key constraint

set FOREIGN_KEY_CHECKS=0;
alter table pessoa drop estado

1828 - Can not drop column 'state': needed in a foreign key constraint 'new / person_ibfk_1'

mysql

asked by anonymous 04.06.2018 / 12:49

1 answer

Error updating entries in the Entity Framework Filter array by first letter

score 3 · Answer 1

You could first remove the foreign key with the following syntax:

ALTER TABLE pessoa DROP FOREIGN KEY NOME_FOREIGN_KEY;

If you need to remove indexes:

ALTER TABLE pessoa DROP INDEX NOME_INDEX;

Then you can proceed with the removal of the column:

ALTER TABLE pessoa DROP COLUMN estado;

For you to check the name of the foreign key and generated indexes, run the following command:

show create table pessoa;