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
), CONSTRAINTrol_permiso_rol
FOREIGN KEYrol_table_fk
(rol_id
) REFERENCESrol
(idrol
)
ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINTrol_permiso_permiso
FOREIGN KEYpermiso_table
(permiso_id
) REFERENCESpermiso
(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
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