I just can’t can find an answer for my question. If there is on stack overflow, just post it and I will delete my post.
My problem:
I want to accept null
values in a mysql column, but not by default. When I insert a row I should also set a value for that column and that should be required.
Right now, in phpmyadmin, if I check Null field (to accept null
values), the field Default (default value) will be automatically set with null
value. That means that I can omit to set a value for that column in an insert
query and it will be filled automatically with a null
value.
When did I say a column should be auto-filled if is not set ? I said to accept null values, not to be auto-filled.
Do I miss something ?
3
Answers
Quoting the docs:
So if your column is nullable, it always has a default value – whether set explicitly (by
DEFAULT ...
clause) or implicitly (like described above).As @Barmar noticed, there’s (at least in theory) a way to handle this with providing an always-failing DEFAULT expression (since MySQL 8.0.13 allowing it).
An alternative (available on versions before 8.0.13) is setting up a DEFAULT value that never should be put in your column anyway AND creating an pre-insert trigger checking the inserted value against this special case.
The benefit of this approach is you’re more flexible in providing specific errors for that case. The downside is the value you might consider special will end up being actually used by real-world scenarios, causing a really weird bug. That’s easier to prevent for numbers (0 for unsigned IDs, etc.), but much harder to do with user-created strings and similar entities.
If a column has the
NULL
attribute, and you don’t specify an explicitDEFAULT
attribute, it’s as if you defined it withDEFAULT NULL
. This is specified in the documentation of "Implicit Default Handling":If you want to prevent omitting the column when inserting, I think you can do it by specifying a
DEFAULT
expression that causes an error. This requires you to be using at least version 8.0.13 of MySQL, because prior to this theDEFAULT
had to be a literal, not an expression.You can do this by creating a column that accepts a null, and then applying a trigger BEFORE INSERT that checks for null.
For example:
Now create a trigger
Attemppting to insert a NULL in the
nullField
column will return an error 45000, but updating a row to contain NULL will succeed.Tested with MySQL 8
Demo: https://www.db-fiddle.com/f/8eV4VihbeB5woEYwuGMFvK/0