1

I was working with a particularly large table (22 fields) and those fields are not always filled on create. In fact on first create, only 9 fields are set.

I was not surprised to get an error that I’m trying to create a row with undefined fields which don’t have default values. I saw two ways of fixing this:

  1. Go in the database and set every field to have default value set to null
  2. When calling create, insert "" for string values, 0 for integer values, etc.

I decided to go with the first option as it is quicker and less messy.

Now I have to go in the database and change each field to have default value of either null or current timestamp if working with date or time.

Is this really the best approach? Is there no way of leaving these fields empty?

Sebastian Simon
  • 18,263
  • 7
  • 55
  • 75
Dimitar
  • 1,148
  • 8
  • 29
  • That depends on your data model. Sometimes you want to state explicitely, that the value is undefined, sometimes you want to pass default value like 0 or empty string. – px1mp Jun 10 '20 at 13:05
  • 1
    Don't use empty strings or 0 for missing values, that's a mess. – jarlh Jun 10 '20 at 13:06
  • @px1mp Lets say i dont want to do that. Is it a good practice to set default values to null or not? – Dimitar Jun 10 '20 at 13:07
  • 'those fields are not always filled on create' - ?? no columns are filled on a create - what did you mean by this? – P.Salmon Jun 10 '20 at 13:08
  • @jarlh Yes it is, i couldnt think of an alternative solution to these 2 and i chose the first one (set default as null) instead of the latter one. – Dimitar Jun 10 '20 at 13:08
  • @P.Salmon I guess i misspoke. They are not filled on inser. Im working with a CRUD api so i messed up the terminology. – Dimitar Jun 10 '20 at 13:09
  • Still not with you - if you insert values into specific columns then the remaining columns will be set to/default to null without having to specify default null in table definition. see https://www.db-fiddle.com/f/r19QtWxwibPXmWXYdreq87/0 – P.Salmon Jun 10 '20 at 13:14
  • @P.Salmon Not really. If you call `INSERT INTO tab (value1 = 1, value2 = 2)` and tab has a `value3` field, if you dont specify a default value in the table structure when creating the table, i will not allow you to insert into that table if you dont specify all the fields (value1, value2 and value3). At least in my scenario that was the case. – Dimitar Jun 10 '20 at 13:17
  • Im using SQLAlchemy o run the queries. My database is hosted on PhpMyAdmin which as far as i know uses innodb as a storage engine. – Dimitar Jun 10 '20 at 13:21
  • True but if you insert into tab(value1,value2) values (1,2) - then it will - or is it a feature of SQLAlchemy that you cannot specify columns in the insert? or are you saying you don't want to? BTW you should tag SQLAlchemy – P.Salmon Jun 10 '20 at 13:21
  • @P.Salmon Im actually using that format of inserting: `INSERT INTO tab (val1, val2) VALUES (%s, %s)` and then i pass in 2 parameters to substutute for each `%s` - a way to defend against `XSS`. It could be that `SQLAlchemy` converts `(val1, val2) VALUES (1, 2)` into `(val1 = 1, val2 = 2)` after it binds parameters and checks for `xss` but i dont think that is likely. – Dimitar Jun 10 '20 at 13:35

1 Answers1

2

If you are inserting rows with 9 values out of 22 values set, then presumably the other 13 columns are nullable or should have a default value.

If there is no obvious default, then the correct data model is to have NULLable columns. That is what the data is and how it should be represented.

Although I don't advocate its use, I will note that '' works for almost all data types. It is converted to a 0 for numbers and to a 0 date for date/time values. Under some circumstances, this can be a handy way to assign a value.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Rick said **"NULL isn't allocated any memory, ... Empty IS allocated to a memory location..."** from his reply ( https://stackoverflow.com/questions/24444518/difference-between-null-and-blank-value-in-mysql ). I think for larger tables such as 22 fields, this is one way to save space but I guess this will slow your search performance. – paolooo Aug 09 '22 at 04:01