Search code examples
mysqldatabasephpmyadmininnodbeasyphp

MySQL #1452 - Cannot add or update a child row

I'm begginer here, all what i'm trying to do is insert into a table field which is a foreign key, please take a look at this two tables :

Table Categorie

CREATE TABLE IF NOT EXISTS `categorie` (
    `id_cat` int(2) NOT NULL AUTO_INCREMENT,   
    `nom_cat` varchar(20) NOT NULL,   
    PRIMARY KEY (`id_cat`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

and table Annonce

CREATE TABLE IF NOT EXISTS `annonce` (
  `id_annonce` int(6) NOT NULL AUTO_INCREMENT,
  `titre` varchar(30) NOT NULL,
  `description` varchar(255) NOT NULL,
  `tarif` float NOT NULL,
  `deplacement` int(2) NOT NULL,
  `date_creation` date NOT NULL,
  `date_expiration` date NOT NULL,
  `image` varchar(255) NOT NULL,
  `id_cat` int(2) DEFAULT NULL,
  PRIMARY KEY (`id_annonce`),
  KEY `id_cat` (`id_cat`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

After linking the foreign key id_cat manually (ON UPDATE SET NULL ON DELETE CASCADE) that's how the db looks like

DB designer

and after inserting data into Categorie table it looks like this

Categorie table

But unfortunately i couldn't execute this query :

INSERT INTO annonce (id_annonce, titre, description, tarif, deplacement, 
                    date_creation, date_expiration,id_cat) 
VALUES('','anything','anything',2,3,'2017-04-01','2017-04-01',2)

the error says :

1452 - Cannot add or update a child row: a foreign key constraint fails (lametcom.annonce, CONSTRAINT annonce_ibfk_2 FOREIGN KEY (id_cat) REFERENCES annonce (id_cat) ON DELETE SET NULL ON UPDATE CASCADE)

Can anyone help please and so sorry about my poor English i hope you can understand what i mean

Solution

  • Your foreign key constraint is wrong. You have

    FOREIGN KEY id_cat REFERENCES annonce (id_cat)

    but it should be:

    FOREIGN KEY id_cat REFERENCES categorie (id_cat)

    The table name in the foreign key constraint has to be the table you're linking to.