I have a table like this:
CREATE TABLE test (
height int(10) CHECK(height>5)
);
When I try to remove check constraint by:
ALTER TABLE test DROP CONSTRAINT height;
I got this error message:
ERROR 1091 (42000): Can't DROP CONSTRAINT `height`; check that it exists
Here is the SHOW CREATE TABLE test; command output:
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`height` int(10) DEFAULT NULL CHECK (`height` > 5)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------+
And here is the SELECT * from information_schema.table_constraints where TABLE_NAME = 'test'; output:
+--------------------+-------------------+-----------------+------------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+------------------+------------+-----------------+
| def | test_db | height | test_db | test | CHECK |
+--------------------+-------------------+-----------------+------------------+------------+-----------------+
CREATE TABLE :: Constraint Expressions
...
MariaDB 10.2.1 introduced two ways to define a constraint:
- CHECK(expression) given as part of a column definition.
- CONSTRAINT [constraint_name] CHECK (expression)
...
If you define the constraint using the first form (column constraint), you can remove it using MODIFY COLUMN:
ALTER TABLE `test`
MODIFY COLUMN `height` INT(10);
If you use the second form (table constraint), you can remove it using DROP CONSTRAINT:
ALTER TABLE `test`
DROP CONSTRAINT `height`;
See dbfiddle.