MySQL accepts CHECK constraint syntax in the CREATE TABLE statement, and gives no error. But
MySQL does not store any information for the constraint, and does not enforce the constraint.
Similarly for foreign key constraints when using MyISAM tables.
IMHO, this is a bad decision on the part of the makers of
MySQL. They did it so that users could import schema definitions from other RDBMS that make use of these features, without editing their DDL scripts. But it causes users to have a false impression that a given constraint has been defined in the schema.
As an alternative, if you use
MySQL 5.0 you can write a trigger that changes your Field1 to NULL if someone attempts to insert a value of "". That way, the NOT NULL constraint on that column will be violated and the operation will be aborted.
CREATE TRIGGER noblank_field1 BEFORE INSERT ON Tablename
FOR EACH ROW BEGIN
IF NEW.Field1 = "" THEN
SET NEW.Field1 = NULL;
END IF;
END
(Also make a similar trigger BEFORE UPDATE.)
If you use a version of
MySQL earlier than 5.0, triggers are not implemented. You would have to enforce the rule in application code.
--
Regards,
Bill K.