non null field accepting blanks

[simpli]

Hi,

I have created a column in mysql with the a utf8 charset and unicode_ci collation. I also set the null options to no, as I do not want null values in that field.

When I enter data in it however I am able to leave it blank and the it's accepted. This is counterintuitive and is contrary to the behavior I want for that field. When I look at the data in navicat or phpmyadmin with the hexa view I see nothing at all so it seems there is really nothing in the field.

Can anyone explain to me why a field that is set to explicitly not accept nulls does accept them. And what can I do to ensure that a field is not empty or null?

 

Thanks for the help,

JR

[dzelenika]

What is datatype of your column?

How have you filled your data (through your application or PHPMyAdmin or other tool)

NOT NULL means that your field (if VARCHAR) may be '' (empty string).

[haku]

NULL is not the same as empty. An empty field contains something - that something being nothing. NULL is more like non-existence.

[corbin]

I remember the day we learned about null sets in math class one day....

 

 

I still think most of the people in there have no idea what the hell a null set is.  lol.

[simpli]

the datatype of my column is indeed varchar. I want it to be not null but I also want it to be not empty. How can I specify this?

[dzelenika]

the datatype of my column is indeed varchar. I want it to be not null but I also want it to be not empty. How can I specify this?

 

Then you have to do validation in PHP code before inserting data.

[simpli]

Yikes,

you mean that with the data type only I can't control that no null or empty string are entered? That's a bit of a surprise I must say.

JR

[fenway]

Yikes,

you mean that with the data type only I can't control that no null or empty string are entered? That's a bit of a surprise I must say.

JR

What do you mean by that? If you pass the DB a blank string, it will store a blank string.  If you want to store "nothing", pass it NULL.

[simpli]

this all started when I wanted to test if my 'non null' specification was working. I basically proceed in navcat to create a record without specifying anything in the 'non null' field, thinking that the record would be rejected as invalid but it wasnt. It was just created with nothing. That was disturbing to me as I thought the 'non null' was supposed to protect from '' as well. What everyone seems to be saying is that specifying that a field does not accept null will not be enough to ensure that it is not empty either. My understanding from the posts is that the validation to prevent the insertion of an empty field must be done in the business logic. Is that correct?

JR

[haku]

That's correct.

 

The reason non-NULL doesn't prevent '' is because '' doesn't equal NULL.

[fenway]

That's correct.

 

The reason non-NULL doesn't prevent '' is because '' doesn't equal NULL.

And it's much easier to think about with numeric fields -- there a big difference between 0 of something (which might be true) and not specified (NULL).  You can't use zero to represent both.

 

Same goes for strings.

[roopurt18]

Going further, NULL isn't equal to anything, not even NULL.

 

If you want to prevent NULLs and empty strings, then:

• declare the column not null
  
• do not specify a default value
  
• add one of the following, depending on what your database implementation supports:
  
  • check constraint
    
  • insert and update triggers