How to delete a foreign key from Mysql

Question

I’m having the same problem in every database. 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 got the following errors when executing the following lines:

alter table pessoa drop estado

1553 - Cannot drop index 'statoAtuall': needed in a Foreign key Constraint

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

1553 - Cannot drop index 'state': needed in a Foreign key Constraint

set FOREIGN_KEY_CHECKS=0;
alter table pessoa drop estado

1828 - Cannot drop column 'status': needed in a Foreign key Constraint 'new/people_ibfk_1'

Answer 1

You could first remove 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 removing the column:

ALTER TABLE pessoa DROP COLUMN estado;

To check the Foreign key name and generated indexes, run the following command:

show create table pessoa;

Answer 2

In case anyone’s still looking for it I got it that way:

show create table pessoa;

you must find something like

CONSTRAINT pessoa_ibfk_1 FOREIGN KEY (estado) REFERENCES estado (id)

Use the command ALTER TABLE pessoa DROP CONSTRAINT pessoa_ibfk_1 to remove Constraint from the status column.

Use ALTER TABLE pessoa DROP INDEX estado; to remove foreign key from the status column.

After these two commands you can remove the column.