How to remove a Foreign key in mysql?

Question

I need to edit a table, but I cannot remove it. I need to remove Foreign key, however, all commands I tried did not work.

I’m currently trying to

alter table tabela drop foreign key nomefk

And the error always happens by saying that there is no foreign key, but when I pull the describe, it exists and prevents me from modifying the table. Describing better what I will do, this table was originally a ratio 1 to N, but I analyzed the problem better and now turned N to N and so I need to remove this key to create it in the additional table.

Answer 1

The command must be:

alter table table drop foreign key nomefk

What is happening is that it is not finding the name of the Foreign key, as the name may have an automatic change of Mysql when it is created. Mysql itself makes the change. What you should do is find the right name for foreing key and then execute the command. To find the name of fk open the tab Schemas which is at the bottom left of the database home screen, right-click on the tabela which is fk, then click on Alter Table. On the next screen look at the bottom of the tab Foreing Keys. In this tab will appear the field Foreing Key Name. You use this name in the command alter table table drop foreign key nomefk. OK

Answer 2

DJM_JM, I tested your command in the database created with the following script:

    mysql> CREATE DATABASE teste;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE `logins` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `nome` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
);

ERROR 1046 (3D000): No database selected

mysql> use teste;
Database changed
mysql>  CREATE TABLE `logins` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `nome` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
);

Query OK, 0 rows affected (2.35 sec)

mysql> CREATE TABLE `posts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `logins_id` int(11) NOT NULL,
    `texto` text,
    PRIMARY KEY (`id`),
    KEY `fk_posts` (`logins_id`),
    CONSTRAINT `fk_posts` FOREIGN KEY (`logins_id`) 
    REFERENCES `logins` (`id`) ON DELETE CASCADE
);

Query OK, 0 rows affected (1.61 sec)

mysql> alter table posts drop foreign key fk_posts;
Query OK, 0 rows affected (2.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

Note that it worked. See if there is no key duplication or error in table names.

Answer 3

Try to fix it, or give up and give another solution...

Attempted correction

DELIMITER \\

SET FOREIGN_KEY_CHECKS=0\\

ALTER TABLE tabela DROP FOREIGN KEY nomefk\\

SET FOREIGN_KEY_CHECKS=1\\

DELIMITER ;

Didn’t it? Redo everything with the following:

DELIMITER \\

--crie uma tebale temporária com a mesma estrutura da outra, mas sem chave primária
CREATE TABLE tabela_tmp (colunas, etc)\\

--passa os dados da 'tabela' para a 'tabela_tmp'
SELECT INTO  tabela_tmp  (colunas, etc) ...
 SELECT FROM tabela\\

-- detorna a tabela anterior
DROP TABLE tabela\\

-- coloca a tabela tmp com o mesmo nome da que vc detonou anteriormente
RENAME TABLE tabela_tmp TO tabela\\

-- coloca a Foreign Key de novo (consulta syntax no site)
ALTER TABLE TABELA
    ADD fk_nome FOREIGN KEY (etc, ...) \\

DELIMITER ;