Error adding FOREIGN KEY to table already created [duplicate]

Question

Error adding FOREIGN KEY to table already created [duplicate]

Navigation

#1 by (0 votes)

-1

I have a table that needs to be changed to have a foreign key

ALTER TABLE bancocliente.producto ADD CONSTRAINT fk_fabPro FOREIGN KEY (cod_fab) REFERENCES bancocliente.fabrica (code);

I get the error message:

Erro SQL (1452): Cannot add or update a child row: a foreign key constraint fails ('bancocliente'.'#sql-2eb8_2', CONSTRAINT 'fk_fabPro' FOREIGN KEY ('cod_fab') REFERENCES 'fabrica' ('code'))

Table creation code:

CREATE TABLE 'produto' (
    'code' INT(11) NOT NULL AUTO_INCREMENT,
    'cod_fab' INT(11) NOT NULL DEFAULT '0',
    'nome' VARCHAR(50) NOT NULL,
    'preco' FLOAT NOT NULL,
    PRIMARY KEY ('code')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

and table manufactures

CREATE TABLE 'fabrica' (
    'code' INT(11) NOT NULL AUTO_INCREMENT,
    'nome' VARCHAR(50) NOT NULL,
    PRIMARY KEY ('code')
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;

Error Image:

mysql sql foreign-key

asked by anonymous 12.07.2018 / 20:50

1 answer

Save positions of a text file in variables using PHP I can not do these steps! I wanted help

score 0 · Accepted Answer

The solution developed together with the help of @RannanLinhalis

Make a backup of the tables to be changed product manufactures

for

producto_bk fabrica_bk

Erase table data

product manufactures

run the sql command: ALTER TABLE bancocliente.producto ADD CONSTRAINT fk_fabPro FOREIGN KEY (cod_fab) REFERENCES bancocliente.fabrica (code);

After these steps, I populated the tables again correctly.