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")
2
Answers
Maybe can you try change the order of create tables? Try create first User and then Orders
Users
firstly thenOrders
.Users.UserID
is defined asVarchar(255)
whereasOrders.User_id
is defined asInt(255)
. You must set the same datatype in both tables. Forid
columnINT
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.