0

enter image description here

how to overcome these type of issues where null value inserted into primary key not null column in table

  • 4
    You're not inserting a NULL value, you're inserting "". – LowlyDBA Jan 25 '15 at 15:28
1

As was stated in the comment

You're not inserting a NULL value, you're inserting ""

If you want to prevent an empty string ("") and NULLs from being used as ai_name, you need a trigger. In your particular case, you need a BEFORE INSERT trigger:

DELIMITER $$

CREATE TRIGGER check_for_blank_ai_name
BEFORE INSERT ON actionitems
FOR EACH ROW  
BEGIN  
  DECLARE dummy INT;
  IF ( IFNULL(NEW.ai_name,'') = '' ) THEN  
    SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';
  END IF;
END $$

DELIMITER ;

The line of code

SELECT 'Any Message' INTO dummy FROM mysql.user WHERE used = 'anything';

will break. That's by design. This will prevent the INSERT from occurring.

I have written triggers that break midstream before in my other posts:

Give it a Try !!!

Not the answer you're looking for? Browse other questions tagged or ask your own question.