MySQL error #1452: Cannot add or update a child row: a foreign key constraint fails

Question

I have been working on MySQL and have come across the error #1452.

Below is my MySQL schema:

CREATE TABLE IF NOT EXISTS `eurobid`.`utente` (
    `username` VARCHAR(45) NOT NULL,
    `nome` VARCHAR(45) NOT NULL,
    `cognome` VARCHAR(45) NOT NULL,
    `email` VARCHAR(45) NOT NULL,
    `password` VARCHAR(45) NOT NULL,
    `indirizzo` VARCHAR(45) NOT NULL,
    `cap` INT NOT NULL,
    `citta` VARCHAR(45) NOT NULL,
    `stato` VARCHAR(45) NOT NULL,
    `admin` TINYINT(1) NULL,
    `feedback` INT NULL,
    `sommafeedback` INT NULL,
    PRIMARY KEY (`username`),
    UNIQUE INDEX `Username_UNIQUE` (`username` ASC))
ENGINE = MyISAM;


CREATE TABLE IF NOT EXISTS `eurobid`.`categorie` (
    `id_categoria` INT NOT NULL AUTO_INCREMENT,
    `tipologia` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id_categoria`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `eurobid`.`pagamento` (
    `id_pagamento` INT NOT NULL AUTO_INCREMENT,
    `tipologia` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id_pagamento`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `eurobid`.`spedizione` (
    `id_spedizione` INT NOT NULL AUTO_INCREMENT,
    `tipo` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id_spedizione`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `eurobid`.`asta` (
    `id_asta` INT NOT NULL AUTO_INCREMENT,
    `titolo` VARCHAR(45) NOT NULL,
    `descrizione` VARCHAR(500) NOT NULL,
    `foto` VARCHAR(45) NULL DEFAULT './images/default.jpg',
    `inizio_asta` DATETIME NOT NULL,
    `fine_asta` DATETIME NOT NULL,
    `prezzo_base` DECIMAL(5) NOT NULL,
    `prezzo_riserva` DECIMAL(5) NULL,
    `in_corso` TINYINT(1) NOT NULL DEFAULT 1,
    `feedback` VARCHAR(250) NULL,
    `vincitore` VARCHAR(45) NULL,
    `id_venditore` VARCHAR(45) NOT NULL,
    `id_categoria` INT NOT NULL,
    `tipo_pagamento` INT NOT NULL,
    `id_spedizione` INT NOT NULL,
    PRIMARY KEY (`id_asta`),
    INDEX `fk_Asta_Utente_idx` (`id_venditore` ASC),
    INDEX `fk_Asta_Categorie1_idx` (`id_categoria` ASC),
    INDEX `fk_Asta_Pagamento1_idx` (`tipo_pagamento` ASC),
    INDEX `fk_Asta_spedizione1_idx` (`id_spedizione` ASC),
    CONSTRAINT `fk_Asta_Utente`
        FOREIGN KEY (`id_venditore`)
        REFERENCES `eurobid`.`utente` (`username`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_Asta_Categorie1`
        FOREIGN KEY (`id_categoria`)
        REFERENCES `eurobid`.`categorie` (`id_categoria`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_Asta_Pagamento1`
        FOREIGN KEY (`tipo_pagamento`)
        REFERENCES `eurobid`.`pagamento` (`id_pagamento`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_Asta_spedizione1`
        FOREIGN KEY (`id_spedizione`)
        REFERENCES `eurobid`.`spedizione` (`id_spedizione`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `eurobid`.`offerta` (
    `id_offerta` INT NOT NULL,
    `prezzo` DECIMAL(5) NOT NULL,
    `data` TIMESTAMP NOT NULL,
    `username` VARCHAR(45) NOT NULL,
    `id_asta` INT NOT NULL,
    PRIMARY KEY (`id_offerta`, `username`, `id_asta`),
    INDEX `fk_offerta_Utente1_idx` (`username` ASC),
    INDEX `fk_offerta_Asta1_idx` (`id_asta` ASC),
    CONSTRAINT `fk_offerta_Utente1`
        FOREIGN KEY (`username`)
        REFERENCES `eurobid`.`Utente` (`username`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_offerta_Asta1`
        FOREIGN KEY (`id_asta`)
        REFERENCES `eurobid`.`Asta` (`id_asta`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
ENGINE = InnoDB;

*I have a problem with the table asta and utente.

When I try to add a new row in phpMyAdmin returns the error #1452 as you can see below:*

#1452 - Cannot add or update a child row: a foreign key constraint fails (`eurobid`.`asta`, CONSTRAINT `fk_Asta_Utente` FOREIGN KEY (`id_venditore`) REFERENCES `utente` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION)

The query I'm having problems with is:

INSERT INTO `eurobid`.`asta` (`id_asta`, `titolo`, `descrizione`, `foto`, `inizio_asta`, `fine_asta`, `prezzo_base`, `prezzo_riserva`, `in_corso`, `feedback`, `vincitore`, `id_venditore`, `id_categoria`, `id_pagamento`, `id_spedizione`) VALUES ('0', 'Aprilia SR 50', 'Bellissimo scooter', './images/default.jpg', '2014-08-29 01:00:00', '2014-08-31 01:00:00', '950', '970', '1', NULL, NULL, 'evin', '1', '2', '3')

Where have I gone wrong? Any help will be much appreciated

i don't understand why the database not recognizes the **username** field. As you can see here: [http://i.imgur.com/R8KyN81.jpg](http://i.imgur.com/R8KyN81.jpg) the user **evin** it's correctly inserted in the table **utente** — Evinrude, Aug 29 '14 at 11:51
Have a look at this; there's a lot more solutions here as well which might give you some more things to check: http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa?rq=1 — jbutler483, Aug 29 '14 at 11:56

score 0 · Answer 1 · answered Aug 29 '14 at 11:45

0

you're creating a table named Asta whilst trying to insert into asta. I'm not used to MySQL but I think it's case sensitive.

there may be other typos, but that's one of them, anyway. Typo's can be the cause.

answered Aug 29 '14 at 11:45

jbutler483

24,074
9
92
145

I tried to change, but i obtain the same result – Evinrude Aug 29 '14 at 11:47

MySQL error #1452: Cannot add or update a child row: a foreign key constraint fails

1 Answers1