Feature-rich ORM for modern Node.js and TypeScript, it supports PostgreSQL (with JSON and JSONB support), MySQL, MariaDB, SQLite, MS SQL Server, Snowflake, Oracle DB (v6), DB2 and DB2 for IBM i.
Context: I'm using sequelize migrations to drop an existing CHECK constraint on one of our tables. The dialect is MySQL Community Server 8.1
When removeConstraint is invoked with correct schema and constraint name, it returns this error: Error Code: 1091. Can't DROP 'constraint_to_delete'; check that column/key exists. I have verified that this constraint definitely exists.
I debugged removeConstraint function further with logging and found out that internally it runs these SQL commands:
SELECT CONSTRAINT_CATALOG AS constraintCatalog, CONSTRAINT_NAME AS constraintName, CONSTRAINT_SCHEMA AS constraintSchema, CONSTRAINT_TYPE AS constraintType,
TABLE_NAME AS tableName, TABLE_SCHEMA AS tableSchema from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name='mytable' AND constraint_name = 'constraint_to_delete' AND TABLE_SCHEMA = 'mydatabase';
DROP INDEX constraint_to_delete on mytable;
Note that it tries to use DROP INDEX to drop the constraint. At least in MySQL DROP INDEX doesnt seem to work for dropping constraints.
If I run this SQL query ALTER TABLE mytable DROP CONSTRAINT constraint_to_delete It gets removed as expected.
Reproducible Example
Here is the link to the SSCCE for this issue: n/a
What do you expect to happen?
The CHECK constraint should be dropped.
What is actually happening?
removeConstraint throws this error: Error Code: 1091. Can't DROP 'constraint_to_delete'; check that column/key exists despite the CHECK constraint definitely existing.
Environment
Sequelize version: 6.32.0
Node.js version: v22.1.0
If TypeScript related: TypeScript version: 4.9.4
Database & Version: MySQL Community Server 8.1
Connector library & Version: mysql2 2.3.3
Would you be willing to resolve this issue by submitting a Pull Request?
[ ] Yes, I have the time and I know how to start.
[ ] Yes, I have the time but I will need guidance.
[x] No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.
Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.
Issue Creation Checklist
Bug Description
Context: I'm using sequelize migrations to drop an existing
CHECK
constraint on one of our tables. The dialect is MySQL Community Server 8.1When
removeConstraint
is invoked with correct schema and constraint name, it returns this error:Error Code: 1091. Can't DROP 'constraint_to_delete'; check that column/key exists
. I have verified that this constraint definitely exists.I debugged
removeConstraint
function further with logging and found out that internally it runs these SQL commands:Note that it tries to use
DROP INDEX
to drop the constraint. At least in MySQLDROP INDEX
doesnt seem to work for dropping constraints.If I run this SQL query
ALTER TABLE mytable DROP CONSTRAINT constraint_to_delete
It gets removed as expected.Reproducible Example
Here is the link to the SSCCE for this issue: n/a
What do you expect to happen?
The CHECK constraint should be dropped.
What is actually happening?
removeConstraint
throws this error:Error Code: 1091. Can't DROP 'constraint_to_delete'; check that column/key exists
despite the CHECK constraint definitely existing.Environment
Would you be willing to resolve this issue by submitting a Pull Request?
Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.