Accept null values but not by default in a MySql column

Question

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 ?

@SamiKuhmonen No value. It should be required on an insert query. — Andrei
– Andrei, Commented Jun 18, 2021 at 22:43
So you want this not to accept nulls on insert, but accept nulls on update? — raina77ow
– raina77ow, Commented Jun 18, 2021 at 22:45
@raina77ow He wants to require the INSERT to specify the value of the column, not allow it to be defaulted. — Barmar
– Barmar, Commented Jun 18, 2021 at 22:47

Barmar · Accepted Answer · 2021-06-18 22:50:20Z

3

If a column has the NULL attribute, and you don't specify an explicit DEFAULT attribute, it's as if you defined it with DEFAULT NULL. This is specified in the documentation of "Implicit Default Handling":

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

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 the DEFAULT had to be a literal, not an expression.

answered Jun 18, 2021 at 22:50

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

raina77ow Over a year ago

I wonder can one overcome the limitation of 8.0.13 by setting up a default value that never gets used in (easier for numbers, much harder for texts), then having an insert/update trigger discarding this value.

Andrei Over a year ago

Is "specifying a DEFAULT expression that causes an error" a good way to do that ? Isn't a bad practice ?

Barmar Over a year ago

@Andrei You want the attempted INSERT to get an error if they don't specify the column, don't you? The DB doesn't have any "good practice" for this.

Barmar Over a year ago

@raina77ow I don't know how a trigger can tell whether the INSERT query has an explicit value for the column.

raina77ow · Accepted Answer · 2021-06-18 23:03:30Z

Quoting the docs:

Implicit Default Handling

If a data type specification includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

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.

Tangentially Perpendicular · Accepted Answer · 2021-06-18 23:26:16Z

1

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:

CREATE TABLE `testNulls` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nullField` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 ;

Now create a trigger

DELIMITER $$
CREATE TRIGGER `testNulls_BEFORE_INSERT` BEFORE INSERT ON `testNulls` FOR EACH ROW BEGIN
  if (new.nullField is null) then  
      signal sqlstate '45000' set message_text = 'Cannot insert NULL in nullField';
      END IF;
END$$
DELIMITER ;

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

answered Jun 18, 2021 at 23:26

Tangentially Perpendicular

5,4364 gold badges15 silver badges34 bronze badges

1 Comment

ysth Over a year ago

but they don't want to prevent nulls from being inserted, just prevent having the column name not be specified on the insert

Collectives™ on Stack Overflow

Accept null values but not by default in a MySql column

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related