SQL 1452 error on my database

Question

I'm trying to update a row in my program. But I keep getting:

MySQLIntegrityConstraintViolationException

which corresponds to a 1452 error according to MySQL workbench. I know this means that the foreign key constraint isn't working, but can't figure out how to fix it.

UPDATE poker_event 
SET prize ='0.00', event_name = 'testdinges', min_players = '4', max_players ='500', loc_name = 'NULL' 
WHERE date_time = '2017-01-26 00:00:00.0';

The error happens when loc_name is set to NULL. This only happens when I delete the location in my program, and after that try to update a row in poker_event.

About my database, here is what MySQl gave me when I use the reverse engineer option. (only relevant parts)

CREATE TABLE IF NOT EXISTS `fullhousegr1`.`location` (
  `loc_name` VARCHAR(255) NOT NULL,
  `house_number` INT(11) NOT NULL,
  `postal_code` VARCHAR(7) NOT NULL,
  `capacity` INT(11) NOT NULL,
  `place` VARCHAR(40) NOT NULL,
  `street` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`loc_name`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS `fullhousegr1`.`poker_event` (
  `event_name` VARCHAR(45) NOT NULL,
  `date_time` DATETIME NOT NULL,
  `min_players` INT(11) NOT NULL,
  `max_players` INT(11) NOT NULL,
  `prize` DECIMAL(10, 2) NOT NULL,
  `loc_name` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`date_time`),
  INDEX `loc_name` (`loc_name` ASC),
  CONSTRAINT `poker_event_ibfk_1`
    FOREIGN KEY (`loc_name`)
    REFERENCES `fullhousegr1`.`location` (`loc_name`)
    ON DELETE SET NULL
    ON UPDATE SET NULL)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

So, what do I have to alter in my database to be able to update a row in poker_event which doesn't have a location?

thanx in advance!

Answer 1

Foreign Key required a value that exist in parent table. NULL is not a value, that's mean we are don't know the value yet. Do not make your foreign key as NULL. Better make it to 0 which you need to add it to parent table.