Prevent having a zero value in a mysql field

ddl, mysql

Solution

You can do this in MySQL 5.5 with the `SIGNAL` syntax in a `TRIGGER`.

Source: http://dev.mysql.com/doc/refman/5.5/en/signal.html

You can do it then with the following `TRIGGER`:

create trigger mytable_zero_check
before insert on my_table
for each row
begin
    if(new.mynum = 0) then
        SIGNAL 'your error message'
    end if 
end

If you are running < 5.5, then you can make a syntax error (for example assigning `NULL` to a field that does not allow `NULL`s), and the `INSERT` will be aborted aswell.

Problem

I have a table like this: ``` CREATE TABLE mytable ( id int(10) unsigned NOT NULL AUTO_INCREMENT, mynum int(10) unsigned NOT NULL, ) ``` The default value for an int is 0 (zero). In my application, 0 is an invalid value for "mynum". How can I ensure that a 0 is never entered for mynum? I can do the check in my application code, but I wish I didn't have to do that. I want mysql to prevent any insert of value 0 in the mynum field. Thanks.

Original source