How to create a CHECK constraint in MySQL that requires negative values?

Question

I want to alter an integer column on a table to add a check constraint preventing the value from being zero or a positive number. For example:

CREATE TABLE example (id INTEGER)
ALTER TABLE example ADD CONSTRAINT chk_negID CHECK (id<0)

MySQL happily complies with these yet then allows the following:

INSERT INTO example VALUES (-1);
INSERT INTO example VALUES (1);

Are my constraints not actually being added? Is there a way to list constraints that have been added to a table after it was created?

This seems like you would need to handle it at the application layer, as MySQL doesn't support this feature. — Ryan, Feb 13 '14 at 00:51
No `CHECK` constraints in MySQL. You need to write a trigger instead. — AgRizzo, Feb 13 '14 at 00:55
@AgRizzo: if only it was a good way to terminate execution from a trigger. — zerkms, Feb 13 '14 at 00:56
Look at this question and the answer http://stackoverflow.com/a/7189396/2587724 - and flip that answer's logic around — AgRizzo, Feb 13 '14 at 01:06

score 4 · Accepted Answer · answered Feb 13 '14 at 00:53

4

It's not supported by mysql (even though it accepts it as a valid syntax)

The CHECK clause is parsed but ignored by all storage engines

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

answered Feb 13 '14 at 00:53

zerkms

249,484
69
436
539

Ryan · Answer 2 · 2014-02-13T01:03:09.780

2

Here is something that would work, but may be hard to debug. This is a trigger:

DELIMITER $$;
CREATE TRIGGER my_trigger AFTER INSERT ON my_table 
FOR EACH ROW
BEGIN
 IF(OLD.id <= 0)
   THEN
   DELETE FROM my_table where id = OLD.id;
 END IF;
END

edited Feb 13 '14 at 01:03

answered Feb 13 '14 at 00:59

Ryan

14,392
8
62
102

To be precise - user may not have `DELETE` permissions – zerkms Feb 13 '14 at 01:00
2

We can't assume he doesn't @zerkms – Ryan Feb 13 '14 at 01:01
indeed. It was just a note that this solution may not work under some circumstances. – zerkms Feb 13 '14 at 01:01
This is an interesting solution, thank you, but the effect is that data successfully `INSERT` suddenly disappears. – Neil C. Obremski Feb 13 '14 at 19:28
If the id is greater than or equal to zero it should be deleted, change the column names to fit your situation. – Ryan Feb 13 '14 at 21:31

How to create a CHECK constraint in MySQL that requires negative values?

2 Answers2

Linked