Bug #17624

can't set TCA field (and therefore db field) to NULL

Added by Klaus Hinum over 11 years ago. Updated over 3 years ago.

Should have
FormEngine aka TCEforms
Target version:
Start date:
Due date:
% Done:


TYPO3 Version:
PHP Version:
Is Regression:
Sprint Focus:


There is no possibility to set a field to NULL (e.g. with checkbox or default option) in the TCA field. This is a major problem as deleting for example integer values lead to "0" in the database (which is different from NULL). Furthermore, the COUNT statement won't work (as i am forced to use string input fields, which also don't allow NULL and only "").

The fix would be to allow the option NULL as default value and for the checkbox in the TCA field.

(issue imported from #M6390)

Related issues

Related to TYPO3 Core - Feature #41773: Add possibility to store NULL values Closed 2012-10-09


#1 Updated by serge vleugels over 11 years ago

Klaus could you provide some more details please. Your example is a bit cryptic.

As I understand you'd like to use the mysql count() function on the field which should have NULL.


#2 Updated by Klaus Hinum over 11 years ago

the problem is, that you can't set a field (using tca) to NULL
see http://www.nabble.com/TCA---set-field-to-NULL-t4428117.html

the problem is, that i need to set fields null and not 0
e.g. for a field that contains the speed of a cpu: null is unknown or not set, 0 = 0 mhz
if the field is a text field, i can set it to "" but the mysql count function only ignores NULL values.
that means counting table columns with "" entries will be counted. therefore, null is also in text fields important.

the functionality to set a value to null is also working in e.g. phpmyadmin where you have a checkbox. therefore, the typo3 tca checkbox option would be ideal, but you can't set it to NULL.

#3 Updated by Steffen Kamper over 11 years ago

INT-values doesn't support NULL as value. You should switch to CHAR or you use in Count-function "where field>0"

#4 Updated by Klaus Hinum over 11 years ago

in typo3 you also can't set CHAR fields to NULL and the count solution with "where field>0" is no solution if fields containing 0 should be counted. there is a difference between 0 and NULL for some applications (like mine). furthermore ordering is also a problem (you have to typecast which costs speed)

phpmyadmin also provides a checkbox to set INT values (and all others) to NULL (if the field supports it), so why not typo3?

#5 Updated by Thorsten Deska over 11 years ago

An other use case could be using this field for money amounts.
€ 0.00 is equal to free but NULL would have the meaning of no information provided.

#6 Updated by Klaus Hinum over 8 years ago

Just wanted to note, that this issue is still pending...

#7 Updated by Antonio Nati over 8 years ago

I also need to set some fields to NULL, and typo3 does not let me to do that.
I need to enforce referential integrity, and NULL value is essential for (missing) foreign keys.

#8 Updated by Andreas Wolf over 7 years ago

  • Category set to FormEngine aka TCEforms
  • Target version deleted (0)

Do you have any good proposal how to display this in the UI? If we keep the checkbox, it should disable the whole field if unchecked. Maybe we could also have the field grayed out as a whole and enable (and thus add) it on-click.

#9 Updated by Klaus Hinum over 7 years ago

I guess the checkbox should be used to really delete the value (NULL if field allows, otherwise set to default if it is not allowed to be empty) and the "0" should be displayed. That would be the most transparent case in my opinion.

#10 Updated by Klaus Hinum about 7 years ago

any progress on that? I think this is a very important feature aka bug!

#11 Updated by Oliver Hader over 6 years ago

See the related issue #41773 which tries to introduce this behaviour. The difficult thing is to bypass several "isset()" checks which will filter out the NULL value.
Besides that, the visualization in the forms is still something to be solved - if the "undefined/NULL" behaviour is applied to a checkbox this will end up in two checkboxes which might look strange...

#12 Updated by Ernesto Baschny over 5 years ago

  • Status changed from New to Closed
  • % Done changed from 0 to 100
  • Is Regression set to No

This is solved meanwhile since 6.0 in #17624.

#13 Updated by Klaus Hinum over 3 years ago

Setting eval=null in tca.php of the extension now works in the Typo3 forms, but how can I change or set a value to NULL in my own extension.
$this->tce = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\DataHandling\\DataHandler');

where I got a "field" => null in the $data array wont work (set to 0). Analyzing the Typo3 field, I see that there is a control[active] value for the checkbox
control[active][tablename][123][tablefield] which is 1 if its null
but how to set this control field in PHP using the DataHandler?

Also available in: Atom PDF