2 years ago

Database Fields: nullable or not?

Posted 2 years ago by intrepidws

Since Laravel 5.4 introduced the ConvertEmptyStringsToNull middleware, I've run into some troubles when doing a mass update on a table with several optional fields. Since empty strings are now converted into NULL, I'm getting database integrity constraints whenever an optional field isn't filled in.

One option would be to pull out all NULL values from my $request->all(), before processing, but that wouldn't really accomplish what I need either. That would mean that a field that previously had a value (and was being updated to NOT have a value) would retain the original value.

There are two obvious solutions, as I see it:

  1. Change all optional database fields to be nullable
  2. Turn off ConvertEmptyStringsToNull

Assuming that I don't want to do #2, this leaves me with changing all of my optional database fields to be nullable.

My question: Is this a bad practice?

One concern that I have is the following:

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

Please sign in or create an account to participate in this conversation.