SQL foreign keys incorrectly formed-CodePudding

Hello dear Stackoverflow users,

I'm working on a database to add to my project. But I'm having struggles figuring out properly making relations in MySQL using PHPmyadmin.

I have written the following:

CREATE TABLE Orders ( 
    OrderID int(255) NOT NULL, 
    Price decimal(65,2), 
    Order_date DATE,
    UserID Int(255) NOT NULL,
    PRIMARY KEY (OrderID), 
    FOREIGN KEY (UserID) REFERENCES Users(UserID) 
); 
CREATE TABLE Users(
    UserID Varchar(255) NOT NULL,
    Password Varchar(12) NOT NULL,
    PRIMARY KEY(UserID)    
);

But I keep getting an error telling me that I incorrectly formed the foreign key. Am I missing something? does PHPmyadmin use a different way of formulating queries?

I have seen sources telling me to use index or later on using table modifiers and indexes to assign primary and foreign keys, but that seemed rather like unnecessary extra steps, unless it's really the only way to do it. My knowledge is yet still limited.

Error message: (errno: 150 "Foreign key constraint is incorrectly formed")

CodePudding user response：

The tables creation order is critical - you cannot refer to the table which is not created. So create Users firstly then Orders.
Your referencing columns datatypes are not compatible - Users.UserID is defined as Varchar(255) whereas Orders.User_id is defined as Int(255). You must set the same datatype in both tables. For id column INT datatype seems to be the most reasonable.

PS. Int(255) is not safe, INTEGER datatype cannot store 255 digits. And the length specifying will be ignored anycase. Moreover, it is deprecated, so remove it at all.

CodePudding user response：

Maybe can you try change the order of create tables? Try create first User and then Orders