How do I require a mysql field?

Question

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)?

Answer 1

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.

---

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sql-mode-important

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_strict_all_tables

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
}

Answer 2

I think you should do two things:

1. Set the column to NOT NULL to force the input of a value
2. 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 BEFORE trigger fails, the operation on the corresponding row is not performed.
• A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.
• An error during either a BEFORE or AFTER trigger 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.

Answer 3

You can set default value for that field: City varchar(40) DEFAULT 'Sandnes'