2

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`)
)`
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
KAMRUL HASAN
  • 45
  • 1
  • 7

1 Answers1

4

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Thank you . I delete old table and create new one and now I can drop check and check name is automatically creating. – KAMRUL HASAN Jul 11 '20 at 07:57
  • In case you are using MariaDB version 10.4.25, you need t use : alter table table_name drop constraint constraint_name; cause check after drop like : alter table table_name drop check constraint_name; will not working. – Noha Salah Jul 19 '23 at 12:34