MySQL: how to prevent insertion of a row with all

In my MySQL table, every column by itself can be NULL, but there must be at least one column with a non-NULL value. At the moment, I am wrapping an insert statement in a stored procedure which prevents insertion of all-NULL rows, but that of course does not keep anyone from using native INSERT statements, circumventing my wrapper procedure.

Is there a 'native' way to define the table with that constraint ?

回答1:

Since MySQL doesn't enforce check constraints, you may want to emulate one with a trigger. I suggest checking out this MySQL Forge article:

Triggers : Emulating Check Constraints

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.

回答2:

grant execute permissions only so users or your application user can only call stored procs !

回答3:

add simple check constraint

CHECK(COALESCE(Field1, Field2, ... FieldN) IS NOT NULL)

UPDATE:

Found that mysql does not support check constraint (parse, but ignore), one is possible workaround: using trigger (look to http://db4free.blogspot.com/2006/01/emulating-check-constraints.html)