I want to DROP check from my table. When I am using this query in MySQL , I am not getting any name of check constraint .
SHOW CREATE TABLE test
CREATE TABLE `test (``ID` int(11) NOT NULL AUTO_INCREMENT,
`price` int(11) DEFAULT NULL CHECK (`price` > 20),
PRIMARY KEY (`ID`)
)`
In MySQL 8.0.16, I tested your table:
mysql> CREATE TABLE test (ID int(11) NOT NULL AUTO_INCREMENT, price int(11) DEFAULT NULL CHECK (price > 20), PRIMARY KEY (ID) );
mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`price` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
CONSTRAINT `test_chk_1` CHECK ((`price` > 20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Now we see the name of the CHECK constraint that MySQL has generated automatically.
mysql> ALTER TABLE test DROP CHECK test_chk_1;
This is documented in https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
| DROP {CHECK | CONSTRAINT} symbol
Once I drop the CHECK constraint, it no longer appears:
mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`price` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
If you use a MySQL version prior to 8.0.16, you don't need to do any of this, because earlier versions of MySQL do not support CHECK constraints. There's nothing to drop, because it does not save the constraint when you create the table.