MySQL ERROR - # 1452 - Can not add or update a child row

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 came up a change for me to do, in the case I have to put the state and city fields. I used this command:

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

But the following error occurs:

  

# 1452 - Can not add or update 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.

    
asked by anonymous 09.02.2015 / 18:46

1 answer

2

Apparently SQL has no error (only one 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)

    
25.02.2015 / 12:09