Mysql ERROR - #1452 - Cannot add or update a Child Row

Asked

Viewed 11,627 times

1

I have the following tables:

CREATE TABLE IF NOT EXISTS `vagas` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `empresa` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `funcao` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `qnt_vagas` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `carga_horaria` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `salario` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `telefone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `estado` int(10) unsigned NOT NULL,
  `cidade` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `estado` (`estado`),
  KEY `cidade` (`cidade`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5;

CREATE TABLE IF NOT EXISTS `cidades` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `estado` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2;

CREATE TABLE IF NOT EXISTS `estados` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT
  `nome` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3;

All have records included, the problem that arose a change for me to do, in case I have to put the state and city fields. I used that command:

ALTER TABLE `vagas` ADD  CONSTRAINT `vagas_fkcidade` FOREIGN KEY (`cidade`) REFERENCES `cidades`(`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

But the following mistake happens:

-#-1452 - Cannot add or update a Child Row: a Foreign key Constraint fails (#sql-de8_d8, CONSTRAINT vagas_fkcidade FOREIGN KEY (cidade) REFERENCES cidades (id) ON DELETE NO ACTION ON UPDATE CASCADE)

I need to include these fields in the table.

mysql
  • 1

    Apparently SQL has no error (only a comma is missing after creating the state id). As to FK possibly has some value in vacancies(city) that does not exist in cities(id)

  • Danilo, the tip given by @Adirkuhn solved your problem?

  • 1

    solved yes thank you very much, actually I had to put a default value in the field and then do the fk

  • @Adirkuhn if you want you can formulate a response as per your comment as it solved the Danilo problem.

1 answer

4


Apparently SQL has no error (only a comma is missing after creating the state id). As for FK possibly has some value in vacancies(city) that does not exist in cities(id)

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.