Since MySQL doesn't enforce check constraints, you may want to emulate one with a trigger. I suggest checking out this MySQL Forge article:
The idea is this to move your check logic to a trigger. If the check fails, call a stored procedure that fails by raising a unique key violation. This allows us to return a descriptive error message back to the client.
Your trigger will probably look something like this:
DELIMITER $$
CREATE TRIGGER check_not_all_null BEFORE INSERT ON your_table FOR EACH ROW
BEGIN
IF COALESCE(field_1, field_2, field_3) IS NOT NULL THEN
CALL fail('All fields cannot be null');
END IF;
END $$
DELIMITER ;
We need to make the fail
sproc raise a unique key violation in order to have the INSERT
aborted when the check fails. The above mentioned article suggests creating a memory table defined as follows:
CREATE TABLE `Error` (
`ErrorGID` int(10) unsigned NOT NULL auto_increment,
`Message` varchar(128) default NULL,
`Created` timestamp NOT NULL default CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ErrorGID`),
UNIQUE KEY `MessageIndex` (`Message`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
Then the fail
sproc could be implemented as follows:
DELIMITER $$
CREATE PROCEDURE `fail`(_message VARCHAR(128))
BEGIN
INSERT INTO error (message) VALUES (_message);
INSERT INTO error (message) VALUES (_message);
END$$
DELIMITER ;
The double INSERT
will ensure that the unique key violation is raised. If the same message already exists in the table, the violation will get raised on the first INSERT
, but it doesn't matter as long as it fails.
We can try the fail
sproc from the command line:
mysql> CALL fail('All fields cannot be null');
ERROR 1062 (23000): Duplicate entry 'All fields cannot be null' for key 2
The good news is that we get back a readable error message. However we don't get back the correct error code, and we don't really have a "duplicate entry". This is obviously one limitation of this method, especially when updating or inserting records in a procedure which uses error handling, in particular handling the 1062 Duplicate Entry
error specifically.