0

I have a PHP form which has both mandatory fields and non mandatory fields. In the corresponding mysql table I have defined mandatory fields are NOT NULL & non mandatory fields will accept NULL values. But I figured out that a user can enter a blank value even for NOT NULL column. What happens is if I leave a NULL column blank, mysql will display its value as NULL and if I leave NOT NULL column blank, mysql will display its value as blank (NULL is not displayed).

What I feel now is it is not necessary to define non-mandatory fields as NULL in corresponding mysql table. What is the best practice? Is it ok to keep them as it is ? (i.e still allowing them to accept NULL value) or shall I change non-mandatory fields also to not to accept NULL values.

Need your advices !!!!!

Gihanmu
  • 407
  • 2
  • 8
  • 15

2 Answers2

0

Would be better if you mention form fields, but if you mean users can enter blank (space character) values in form inputs, you should filter input data to avoid inserting blank values to database. I think you should just control mandatory fields in php code, and table columns null/not null definition is based on type of your input fields and relation between other tables only.

OmidAhn
  • 1
  • 1
0

There are opinions on both sides of the fence, but what makes the most sense to me is to specify "NOT NULL" for all fields, with a default value of your choice—usually a blank screen, but for a date it could be different. That is what I do with most of my tables.

That being said, please refer to the arguments for the other side here and here.

There are several reasons why I and may people don't use NULL values except in special cases. One is that it makes your querying easier. You know you may have blank values, but you know you don't have NULL values, and you don't have to search for both.

I seem to recall also an issue of indexing performance on some engines with NULL values, but that may no longer be an issue on MySQL 5.6.

One more thing to consider is the meaning of a NULL or blank value. For argument's sake, if the John's graduation date is not filled in your database, does that mean:

  1. that John has not graduated?
  2. that he has graduated but we do not know when?
  3. or that we don't know whether he has graduated?

This is something to consider when designing your tables.

Community
  • 1
  • 1
zx81
  • 41,100
  • 9
  • 89
  • 105