Question 113488 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: How to set default value if provded value is null

I have a column in my database table where the default needs to be ‘int 0’ but also null can be provided due to legacy.

I have attached a picture to show what I am trying to do.

The problem I am having is that when I untick ‘Not Null’ and have a default of 0, and a null value is provided then null is added.

But if I tick the box and null is provided it does not add 0, my code throws an exception instead stating the value for this column must not be null.

I want it do that if null is provided then it defaults to 0.

Appreciate any help on this. Thanks

Note: I am using Datagrip

Example

I am assuming something in ‘default expression’ but I am not sure what

Tags:

2

Answers


  1. 0

    Check Data on controller before sending it to database 

    if(testfield == null) { tedtfield = 0;}
    Login or Signup to reply.
  2. 0

    You could create a BEFORE INSERT trigger:

    CREATE TRIGGER mytable BEFORE INSERT ON mytable FOR EACH ROW 
   SET NEW.testfield = COALESCE(NEW.testfield, 0);

    More information on triggers can be found in the documentation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search