Set Doctrine Entity Boolean Field to 0 Instead of Null
When working with Doctrine and Oracle databases, a subtle but persistent issue often arises with boolean fields: setting a property to false results in NULL being stored instead of 0. This behavior can lead to ORA-01400: cannot insert NULL errors when the column is defined as non-nullable.
This discrepancy stems from how Doctrine maps PHP booleans to SQL types and how it handles default values during hydration and persistence. When you define a field like this:
/**
* @ORM\Column(name="substitute", type="boolean")
*/
private $isSubstitute = false;
and then persist an entity with $entity->setIsSubstitute(false), Doctrine may not explicitly set the value in the SQL parameter if it treats false as "no value" or relies on a default that isn't being applied correctly. This is especially problematic when combined with Oracle, which does not allow NULL in non-nullable columns unless explicitly inserted.
Even setting options={"default":"0"} in the annotation doesn't guarantee the desired outcome if Doctrine doesn't use the default during insert. The ORM might still send a parameter that resolves to NULL, triggering the constraint violation.
Understanding Doctrine's Boolean Handling
Doctrineâs boolean type is mapped to SQL using platform-specific types:
- On MySQL,
booleanmaps toTINYINT(1) - On PostgreSQL, it maps to
BOOLEAN - On Oracle, there's no native boolean type, so it usually maps to
NUMBER(1)
However, Doctrine still uses PHP booleans (true/false) for entity properties. When persisting, Doctrine converts these to SQL values. But if the field is nullable and you set it to false, Doctrine may skip sending a value altogether, leading to NULL.
Ensuring Non-Nullable Boolean Fields Store 0 or 1
To fix this, ensure that:
- The column is defined as non-nullable.
- A default value of
0is set at the database level. - Doctrine is configured not to send
NULLwhen a boolean is explicitlyfalse.
Hereâs how to configure the entity correctly:
/**
* @ORM\Column(name="substitute", type="boolean", nullable=false, options={"default"=0})
*/
private $isSubstitute = false;
Note that the default must be a numeric 0, not a string "0". Oracle and some other platforms interpret string defaults differently.
Additionally, make sure your entity doesn't rely on implicit nullability. If the field is marked as nullable=false, Doctrine will attempt to insert a value even if none is set explicitlyâbut only if the mapping is correct.
Database-Level Defaults Are Crucial
Sometimes the issue isn't in the entity mapping but in the database schema itself. Even with options={"default": 0} in Doctrine, if the column doesn't have a default at the DB level, Oracle will insert NULL unless you explicitly provide a value.
You should alter your table to include a default:
ALTER TABLE MYTABLE MODIFY (substitute NUMBER(1) DEFAULT 0 NOT NULL);
This ensures that even if Doctrine sends no parameter for the field, Oracle uses the schema-level default.
Testing the Fix
After making these changes, try persisting an entity with false:
$test = new TestEntity();
$test->setIsSubstitute(false);
$em->persist($test);
$em->flush();
Now, when you query back from the database, you should see 0 instead of NULL. You can verify this by running a direct SQL query or using Doctrine's debug tools.
Common Pitfalls to Avoid
- Using string defaults: Always use numeric
0, not"0". - Relying solely on Doctrine defaults: Some platforms require DB-level defaults for non-nullable columns.
- Assuming boolean mapping is consistent: Always check how your specific platform interprets boolean fields.
By aligning Doctrineâs behavior with database constraints and defaults, you can ensure that boolean fields reliably store 0 or 1, avoiding null-related errors in production environments.