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
, CONSTRAINTvagas_fkcidade
FOREIGN KEY (cidade
) REFERENCEScidades
(id
) ON DELETE NO ACTION ON UPDATE CASCADE)
I need to include these fields in the table.