How to remove a foreign key in mysql?

Question

How to remove a foreign key in mysql?

Navigation

#1 by (0 votes)
#2 by (0 votes)

5

I need to edit a table, but I can not remove it. I need to remove the foreign key, but all the commands I tried did not work.

I'm currently tempting the

alter table tabela drop foreign key nomefk

And the error always happens saying that there is no foreign key, however, when I pull in the describe, it does exist and prevents me from modifying the table. I would like to know if the table is a 1 to N ratio, but I have analyzed the problem better and now it has turned N to N, so I need to remove this key to create it in the additional table.

mysql sql

asked by anonymous 14.02.2018 / 19:22

2 answers

Infinite Scroll ASP NET MVC C # How can I prevent a user from sending the same form multiple times?

score 0 · Answer 1

DJM_JM, I tested your command on the bank 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 or error duplication in table names.

score 0 · Answer 2

The command should be:

alter table table drop foreign key nombrefk

What is happening is that it is not finding the foreign key name, as the name may have an automatic MySQL change when it is created. MySQL itself makes the change. What you should do is find the correct name of foreing key and then run the command. To find the fk name open the Schemas tab that is in the lower part of the left side of the initial screen of the database, right-click on the tabela that is at fk, then click on Alter Table . On the next screen, look under the Foreing Keys tab. In this tab the Foreing Key Name field will appear. You use this name in the command alter table table drop foreign key nombrefk. Ok