MySQL error when trying to create many to many relationship

Question

Im trying to create a many to many relationship between 'Rol' and 'Permisos', here is my code

Table 'Rol':

CREATE TABLE IF NOT EXISTS `tienda_v1`.`rol` (
  `idrol` INT NOT NULL AUTO_INCREMENT,
  `nombre_rol` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idrol`))
ENGINE = InnoDB;

Table 'Permiso':

CREATE TABLE IF NOT EXISTS `tienda_v1`.`permiso` (
  `idpermiso` INT NOT NULL AUTO_INCREMENT,
  `nombre_permiso` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idpermiso`))
ENGINE = InnoDB;

And here is what I'm trying:

CREATE TABLE `tienda_v1`.`rol_permiso`(
    `rol_id` INT NOT NULL,
    `permiso_id` INT NOT NULL,
    PRIMARY KEY(`rol_id`,`permiso_id`),
    CONSTRAINT `rol_permiso_rol`
        FOREIGN KEY `rol_table_fk` (`rol_id`) REFERENCES `rol` (`idrol`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `rol_permiso_permiso`
        FOREIGN KEY `permiso_table` (`permiso_id`) REFERENCES `permiso` (`idpermiso`)
        ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = InnoDB;

And throws me this Output:

17:25:39 CREATE TABLE tienda_v1.rol_permiso( rol_id INT NOT NULL, permiso_id INT NOT NULL, PRIMARY KEY(rol_id,permiso_id), CONSTRAINT rol_permiso_rol
FOREIGN KEY rol_table_fk (rol_id) REFERENCES rol (idrol)
ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT rol_permiso_permiso FOREIGN KEY permiso_table (permiso_id) REFERENCES permiso (idpermiso) ON DELETE CASCADE ON UPDATE CASCADE )ENGINE = InnoDB Error Code: 3780. Referencing column 'rol_id' and referenced column 'idrol' in foreign key constraint 'rol_permiso_rol' are incompatible. 0.000 sec

Anyone have any idea whats happening? Please help

nbk nbk · Accepted Answer · 2021-06-11T22:38:23

There is no problem with the tables provided.

Maybe you are using diffrent tables in your database?

run

 SHOW CREATE TABLE rol

and check if it is the same

CREATE TABLE IF NOT EXISTS `rol` (
  `idrol` INT NOT NULL AUTO_INCREMENT,
  `nombre_rol` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idrol`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `permiso` (
  `idpermiso` INT NOT NULL AUTO_INCREMENT,
  `nombre_permiso` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idpermiso`))
ENGINE = InnoDB;

CREATE TABLE `rol_permiso`(
    `rol_id` INT NOT NULL,
    `permiso_id` INT NOT NULL,
    PRIMARY KEY(`rol_id`,`permiso_id`),
    CONSTRAINT `rol_permiso_rol`
        FOREIGN KEY `rol_table_fk` (`rol_id`) REFERENCES `rol` (`idrol`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `rol_permiso_permiso`
        FOREIGN KEY `permiso_table` (`permiso_id`) REFERENCES `permiso` (`idpermiso`)
        ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = InnoDB;

db<>fiddle here

MySQL error when trying to create many to many relationship

1 Answers