mysql constraint check produces error

Question

For homework we have to craft an sql statement that does quite a few things at once. I'll post it here

ALTER TABLE assn7 ADD gpa float DEFAULT 4.0 CONSTRAINT gpacheck check(gpa >= 0.0 AND gpa <=4.0);

It should be obvious what I'm trying to do but I get an error

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 gpacheck check(gpa >= 0.0 AND gpa <=4.0)' at line 2

it works if I take out the constraint check but we need it to be one big sql statement.

Answer 1

your query statement is bit wrong. Per MySQL manual it should be

ALTER TABLE assn7 ADD gpa float DEFAULT 4.0;
ALTER TABLE assn7 ADD CONSTRAINT gpacheck check(gpa >= 0.0 AND gpa <=4.0);

You can't mix both adding up a column and constraint together.

Though to point out; there is no Check constraint support in MySQL. Per manual it says

The CHECK clause is parsed but ignored by all storage engines.

Alternative, is to go for triggers (BEFORE INSERT TRIGGER)

Answer 2

Check constraints don't work in MySQL, but you can achieve your statement in one line by adding a comma like this:

ALTER TABLE assn7 ADD gpa float DEFAULT 4.0, ADD CONSTRAINT gpacheck check(gpa >= 0.0 AND gpa <=4.0);