MYSQL Error Code: 1005. Can't create table `db_xcruz`.`users` (errno: 150 "Foreign key con-CodePudding

I'm having the #1005 error code in MySQL workbench and errno 150:

MYSQL Error Code: 1005. Can't create table db_xcruz.users (errno: 150 "Foreign key constraint is incorrectly formed")

I don't know what to do, i tried everything.

Here's the code

###Database creation###

CREATE DATABASE IF NOT EXISTS `DB_XCRUZ`;
USE `DB_XCRUZ`;

###Tables creation###

    /*Users - Table*/
DROP TABLE IF EXISTS `USERS`;
CREATE TABLE `USERS`(
    `ID` INT NOT NULL,
    `FIRST_NAME` VARCHAR(100) DEFAULT NULL,
    `LAST_NAME` VARCHAR(100) DEFAULT NULL,
    `EMAIL` VARCHAR(320) DEFAULT NULL,
    `CATEGORY_ID` SMALLINT(3) NOT NULL,
    `CART_ID` INT NOT NULL,
    
    PRIMARY KEY (`ID`),
    FOREIGN KEY (`CATEGORY_ID`) REFERENCES USER_CATEGORIES(`ID`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`CART_ID`) REFERENCES CARTS(`ID`) ON UPDATE CASCADE ON DELETE CASCADE

);

    /*User categories - Table*/
DROP TABLE IF EXISTS `USER_CATEGORIES`;
CREATE TABLE `USER_CATEGORIES`(
    `ID` SMALLINT(3) NOT NULL,
    `NAME` VARCHAR(80) DEFAULT NULL,
    
    PRIMARY KEY (`ID`)
    
);


    /*Carts - Table*/
DROP TABLE IF EXISTS `CARTS`;
CREATE TABLE `CARTS`(
    `ID` INT NOT NULL,
    `SUBTOTAL` DECIMAL(11,2) DEFAULT NULL,
    `TOTAL` DECIMAL(11,2) DEFAULT NULL,
    
    PRIMARY KEY (`ID`)
    
);

CodePudding user response：

create your foreign tables first before creating the user table.

###Database creation###

CREATE DATABASE IF NOT EXISTS `DB_XCRUZ`;
USE `DB_XCRUZ`;

###Tables creation###
/*User categories - Table*/
DROP TABLE IF EXISTS `USER_CATEGORIES`;
CREATE TABLE `USER_CATEGORIES`(
    `ID` SMALLINT(3) NOT NULL,
    `NAME` VARCHAR(80) DEFAULT NULL,
    
    PRIMARY KEY (`ID`)
    
);

/*Carts - Table*/
DROP TABLE IF EXISTS `CARTS`;
CREATE TABLE `CARTS`(
    `ID` INT NOT NULL,
    `SUBTOTAL` DECIMAL(11,2) DEFAULT NULL,
    `TOTAL` DECIMAL(11,2) DEFAULT NULL,
    
    PRIMARY KEY (`ID`)
    
);


/*Users - Table*/
DROP TABLE IF EXISTS `USERS`;
CREATE TABLE `USERS`(
    `ID` INT NOT NULL,
    `FIRST_NAME` VARCHAR(100) DEFAULT NULL,
    `LAST_NAME` VARCHAR(100) DEFAULT NULL,
    `EMAIL` VARCHAR(320) DEFAULT NULL,
    `CATEGORY_ID` SMALLINT(3) NOT NULL,
    `CART_ID` INT NOT NULL,
    
    PRIMARY KEY (`ID`),
    FOREIGN KEY (`CATEGORY_ID`) REFERENCES USER_CATEGORIES(`ID`) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`CART_ID`) REFERENCES CARTS(`ID`) ON UPDATE CASCADE ON DELETE CASCADE

);

CodePudding user response：

tried deleting the DB_XCRUZ database and recreating it