I just discovered NOT NULL does not make a field required.
When creating a mysql table, how do I create a field that cannot contain null or blank (must have something in it)?
103 Answers
By default, MySQL accepts invalid values. You can set MySQL to strict mode to force valid values. This will reject a query that does not provide a value for a NOT NULL column as well as enforce integrity on all types of columns.
Update: MySQL 5.7 and above now have strict mode on by default. So it would not accept invalid values by default like previous versions.
Edit:
@Barranka and @RocketHazmat made good points in the comments. '' is not the same as null, so MySQL will allow that in a NOT NULL column. In that instance, you would have to resort to your code or a trigger.
In the code (PHP for example), this could be easy enough, running something like:
if (!strlen($value)) { // Exclude value or use NULL in query
}I think you should do two things:
- Set the column to
NOT NULLto force the input of a value - Use a trigger to validate the values.
Within the trigger you can cancel the operation if the desired column does not fulfill a required condition (for example, having zero-length).
This question and its answers address this second thing, and here is an example:
delimiter $$
CREATE TRIGGER `cancel_insert_if_empty`
BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN declare msg varchar(255); if NEW.your_column is null or length(NEW.your_column) = 0 then set msg = "You're doing something wrong! Now suffer the consequences"; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg; end if;
END$$
delimiter ;In this example, if you try to insert a null value or a zero-length string in your_column an error will rise and the insert will be canceled. Quoting from the reference manual:
MySQL handles errors during trigger execution as follows:
- If a
BEFOREtrigger fails, the operation on the corresponding row is not performed.- A
BEFOREtrigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.- An error during either a
BEFOREorAFTERtrigger results in failure of the entire statement that caused trigger invocation.
Of course, you can write a trigger to check the updates too.
Hope this helps.
1You can set default value for that field: City varchar(40) DEFAULT 'Sandnes'
2
