simpli Posted June 29, 2009 Share Posted June 29, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/ Share on other sites More sharing options...
dzelenika Posted June 29, 2009 Share Posted June 29, 2009 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). Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-865265 Share on other sites More sharing options...
haku Posted June 29, 2009 Share Posted June 29, 2009 NULL is not the same as empty. An empty field contains something - that something being nothing. NULL is more like non-existence. Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-865377 Share on other sites More sharing options...
corbin Posted June 29, 2009 Share Posted June 29, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-865387 Share on other sites More sharing options...
simpli Posted June 29, 2009 Author Share Posted June 29, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-865538 Share on other sites More sharing options...
dzelenika Posted June 29, 2009 Share Posted June 29, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-865540 Share on other sites More sharing options...
simpli Posted June 29, 2009 Author Share Posted June 29, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-865619 Share on other sites More sharing options...
fenway Posted June 30, 2009 Share Posted June 30, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-866661 Share on other sites More sharing options...
simpli Posted June 30, 2009 Author Share Posted June 30, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-866792 Share on other sites More sharing options...
haku Posted July 1, 2009 Share Posted July 1, 2009 That's correct. The reason non-NULL doesn't prevent '' is because '' doesn't equal NULL. Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-866897 Share on other sites More sharing options...
fenway Posted July 1, 2009 Share Posted July 1, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-867333 Share on other sites More sharing options...
roopurt18 Posted July 1, 2009 Share Posted July 1, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/164019-non-null-field-accepting-blanks/#findComment-867348 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.