CODEWITHSUNDEEP
mysql
Neil C. Obremski
Neil C. Obremski

Reputation: 20324

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

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?

Upvotes: 3

Views: 3157

Answers (2)

Ryan
Ryan

Reputation: 14659

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

Upvotes: 2

zerkms
zerkms

Reputation: 255025

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

Upvotes: 4

Related Questions