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")
- The tables creation order is critical – you cannot refer to the table which is not created. So create
- Your referencing columns datatypes are not compatible –
Users.UserIDis defined as
Orders.User_idis defined as
Int(255). You must set the same datatype in both tables. For
INTdatatype seems to be the most reasonable.
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.
Answered By – Akina
Answer Checked By – Pedro (BugsFixing Volunteer)