Error adding foreign key, how to proceed?

Asked

Viewed 168 times

1

I am trying to reference the column id of a table in another table in my database, but I am told an error that is not very specific. The engine being used is Innodb and the database is in Mysql. It follows script generated by the database and error reported respectively

Sequel:

ALTER TABLE `service_desk_pi`.`chamado` 
ADD CONSTRAINT `estrangeiraEquipamento`
  FOREIGN KEY (`fkIdEquipamento`)
  REFERENCES `service_desk_pi`.`equipamento` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Error:

Executing:
ALTER TABLE `service_desk_pi`.`chamado` 
ADD CONSTRAINT `estrangeiraEquipamento`
  FOREIGN KEY (`fkIdEquipamento`)
  REFERENCES `service_desk_pi`.`equipamento` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1215: Cannot add foreign key constraint
SQL Statement:
ALTER TABLE `service_desk_pi`.`chamado` 
ADD CONSTRAINT `estrangeiraEquipamento`
  FOREIGN KEY (`fkIdEquipamento`)
  REFERENCES `service_desk_pi`.`equipamento` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE
mysql sql database syntax foreign-key

  • Here REFERENCES service_desk_pi should probably be the name of your equipment table.

  • This script with this syntax is generated by the database itself, right after REFERENCES service_desk_pi one has . equipamento (id) which references the table name and also the column name. Can the database itself make a mistake in the syntax of the script it produces?

  • Has the table that you are trying to reference with the foreign key been successfully inserted? Could you put in its script as well?

  • The table is already persisted in the database. Here is the script: CREATE TABLE equipamento ( id int(11) NOT NULL AUTO_INCREMENT, fk_chamado varchar(45) NOT NULL, marca varchar(45) NOT NULL, modelo varchar(45) NOT NULL, categoria varchar(45) NOT NULL, PRIMARY KEY (id) ) ENGINE=Myisam DEFAULT CHARSET=latin1; ?

1 answer

1


Are you sure the table Engines are the same? If the type of fields and engine are equal the code is correct.

  • It was exactly this, I just checked and saw that the table that would be referenced was in Myisam, while the table that would make the reference was in Innodb. The mistake was to think that the engine applied to the whole bank instead of the tables. Thank you.

Browser other questions tagged mysql sql database syntax foreign-key

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