MySQL Drop Constraint Syntax Error

Question

I'm using MySQL 5.1.36 I'm trying to drop a constraint named chk1 but MySQL reports an syntactical error. I looked it up but am unable to find it. What is the error?

mysql> create database house;
Query OK, 1 row affected (0.01 sec)
mysql> use house;
Database changed
mysql> create table member(age integer);
Query OK, 0 rows affected (0.05 sec)

mysql> alter table member add constraint chk1 check(age>0);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table member drop constraint chk1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint chk1' at line 1

mysql> alter table member drop check chk1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check chk1' at line 1

a1ex07 · Accepted Answer · 2011-02-10 16:52:00Z

1

Check constraint is parsed but ignored in Mysql. So you don't need to remove it. You can use enum or triggers to implement desired functionality. In your case you can also change age to unsigned.

answered Feb 10, 2011 at 16:52

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user191776 Over a year ago

Is there a way to force this constraint without using workarounds like enum or triggers?

a1ex07 Over a year ago

In my understanding changing age to int unsigned or SMALLINT unsigned will do the job in this case. In same cases, FOREIGN KEY will work instead of enums. In general - no, you cannot enforce arbitrary check constraints

user191776 Over a year ago

What would I do if I had a constraint like check(age>40) ?

a1ex07 Over a year ago

I can see 2 ways : 1. Create before insert and before update triggers and raise an error if age is wrong. 2. If you can enumerate allowed ages (for example, if the field represents human age, it will have upper limit 200 years which is pretty optimistic). So you can create another table with all possible ages and add a foreign key to the old table.

Collectives™ on Stack Overflow

MySQL Drop Constraint Syntax Error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related