DROP CONSTRAINT is not able to find constraint

Question

I try to drop a constraint:

USE `mydb`;

BEGIN;
ALTER TABLE `mydb` DROP CONSTRAINT `myconstraint`;
COMMIT;

And it replies with:

ERROR 1091 (42000) at line 6: Can't DROP CONSTRAINT `myconstraint`; check that it exists

But the constraint exists:

MariaDB [(mydb)]> select * from information_schema.table_constraints WHERE table_name = 'mytable';
+--------------------+-------------------+---------------------------------------------------------------+--------------+-----------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME                                               | TABLE_SCHEMA | TABLE_NAME      | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------------------------------------------------+--------------+-----------------+-----------------+
| def                | mydb              | PRIMARY                                                       | mydb         | mytable         | PRIMARY KEY     |
| def                | mydb              | name                                                          | mydb         | mytable         | UNIQUE          |
| def                | mydb              | myconstraint                                                  | mydb         | mytable         | FOREIGN KEY     |
+--------------------+-------------------+---------------------------------------------------------------+--------------+-----------------+-----------------+

What am I doing wrong?

Possible duplicate of [How to remove constraints from my MySQL table?](https://stackoverflow.com/questions/14122031/how-to-remove-constraints-from-my-mysql-table) — Uueerdo, Feb 18 '19 at 18:11

score 2 · Answer 1 · answered Feb 19 '19 at 10:15

2

The problem is that MariaDB (MySql?) does not allow to remove foreign key constraints with the syntax DROP CONSTRAINT. It must be dropped like this:

ALTER TABLE `mytable` DROP FOREIGN KEY `myconstraint`;

answered Feb 19 '19 at 10:15

volingas

1,023
9
21

score 1 · Answer 2 · answered Feb 19 '19 at 04:21

1

I think you are confusing "schema" (aka "database") and "table"): ALTER TABLE mytable ...

answered Feb 19 '19 at 04:21

Rick James

135,179
13
127
222

My constraint is on a a table. I can find the constraint, but I can not drop it. – volingas Feb 19 '19 at 09:22
Try `USE mydb; ALTER TABLE mytable DROP CONSTRAINT myconstraint;` – Rick James Feb 19 '19 at 15:04
Which is what I have in my question and is not working. See my answer for the solution – volingas Feb 20 '19 at 11:49

score 0 · Answer 3 · answered Dec 31 '19 at 12:19

This issue is documented in https://jira.mariadb.org/browse/MDEV-17599. It was fixed in version 10.2.22 and 10.3.13 (11-02-2019). I sugest update MariaDB version or, like @volingas wrote, the sintax:

ALTER TABLE `mytable` DROP FOREIGN KEY `myconstraint`;

DROP CONSTRAINT is not able to find constraint

3 Answers3