Prevent having a zero value in a mysql field

Question

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.

You have to do it in your application code. There is no `NOT 0` equivalent to `NOT NULL`. You might also try using a trigger, but your simplest and best bet is to enforce it in application logic. — elixenide, Dec 06 '13 at 22:08
This could be done via `TRIGGER`s. I would need more information though as to what you mean by "preventing". Should it change the value to something else, should it fully abort? — skiwi, Dec 06 '13 at 22:08
@EdCottrell: well, there is a `NOT 0` equivalent: it's called a check constraint - only MySQL doesn't support it. So it needs to be done in the application and you need to hope that your SQL scripts are all correct. — , Dec 06 '13 at 22:21
Fair enough; I should have specified that there isn't an equivalent in MySQL. You can [emulate the constraint with triggers](http://stackoverflow.com/questions/14247655/mysql-check-constraint), but application logic is probably the best bet. — elixenide, Dec 06 '13 at 22:24

score 2 · Accepted Answer · answered Dec 09 '13 at 07:53

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 NULLs), and the INSERT will be aborted aswell.

Prevent having a zero value in a mysql field

1 Answers1