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

Solution 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
}

Solution 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.

Comments

[Barranka]

This won't work if the user specifically inserts a zero-length string. Example: insert into a_table(a_column) values('');

[Xi Vix]

can I do this just for a specific table or query or is it global?

[Devon]

I'm not sure about per table. I know you can set it in the config file and you can set it in each session SET sql_mode = ...;

[Devon]

I like to keep strict mode on in all of my databases, then program with that in mind. This makes sure that your program isn't inserting any invalid values as well. You set the parameters for the columns when creating the table, why not abide by them?

[Devon]

@Barranka, true. But technically that isn't null. So MySQL won't know the difference and then yes, you would need something like your answer.

[Xi Vix]

thanks a bunch but I was trying to avoid using a trigger so I went with Devon and the strict approach.