So I'm having trouble creating two databases called role_perm
and user_role
. I just couldn't find out where the problem is, since it shows me:
Foreign key constraint is incorrectly formed
For both role_perm
and user_role
, there are five procedures that automatically creates my DBs, these codes are my MySQL codes...
CREATE TABLE `users` (
ID INT(8) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`firstname` VARCHAR(45) NOT NULL,
`lastname` VARCHAR(45) NOT NULL,
`mobile` VARCHAR(45) NOT NULL,
`home` VARCHAR(45) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE = InnoDB;
CREATE TABLE `roles` (
`role_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`role_name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`role_id`)
) ENGINE = InnoDB;
CREATE TABLE `permissions` (
`perm_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`perm_desc` VARCHAR(50) NOT NULL,
PRIMARY KEY (`perm_id`)
) ENGINE = InnoDB;
CREATE TABLE `role_perm` (
`role_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`perm_id` INT(8) NOT NULL,
INDEX (`role_id`, `perm_id`),
FOREIGN KEY (role_id) REFERENCES roles(role_id),
FOREIGN KEY (perm_id) REFERENCES permissions(perm_id)
) ENGINE = InnoDB;
CREATE TABLE `user_role` (
`user_id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`role_id` INT(8) NOT NULL,
INDEX (`user_id`, `role_id`),
FOREIGN KEY (user_id) REFERENCES users.ID,
FOREIGN KEY (role_id) REFERENCES roles.role_id
) ENGINE = InnoDB;