blipman17 blipman17 - 6 months ago 14
MySQL Question

SQL 1452 error on my database

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

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.

Comments