How to fix foreign key errors in MySQL?

Question

I am using Ubuntu on Virtualbox. I’m trying to write my SQL for a database however whenever I create one with a Foreign Key it always returns an error

CREATE TABLE Vehicles (Vehicle ID int, Vehicle Type VARCHAR(255), Model VARCHAR(255), Engine Size float, Condition VARCHAR(255), Price float, PRIMARY KEY (Vehicle ID), FOREIGN KEY (Model) REFERENCES Models(Model));

Can you help me please? Below are my SQL table statements to create the Database so maybe there is an error in my code?

CREATE TABLE Models (
     Model_ID int NOT NULL,
     Manufacturer VARCHAR(255) NOT NULL,
     Model_name VARCHAR(255) NOT NULL,
     Wheel_Drive_Type VARCHAR(255) NOT NULL,
     PRIMARY KEY (Model_ID)); 

CREATE TABLE Customers (
     Customer_ID int NOT NULL,
     Customer_name VARCHAR(255) NOT NULL,
     Customer_Contact_number VARCHAR(255) NOT NULL,
     Customer_Address VARCHAR(255) NOT NULL,
     Customer_Email VARCHAR(255) NOT NULL,
     PRIMARY KEY (Customer_ID)); 

CREATE TABLE Vehicles (
     Vehicle_ID int NOT NULL,
     Vehicle_Type VARCHAR(255) NOT NULL,
     Model_name VARCHAR(255) NOT NULL,
     Engine_Size float NOT NULL,
     Condition VARCHAR(255) NOT NULL,
     Price float NOT NULL,
     PRIMARY KEY (Vehicle_ID),
     FOREIGN KEY (Model) REFERENCES Models(Model_name)); 

CREATE TABLE Enquiries (
     Enquiry_ID int NOT NULL,
     Vehicle int NOT NULL,
     Customer_ID int NOT NULL,
     Additional_information VARCHAR(255) NOT NULL,
     PRIMARY KEY (Enquiry_ID),
     FOREIGN KEY (Vehicle_ID) REFERENCES Vehicles(Vehicle_ID),
     FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)); 

CREATE TABLE Sales (
     Sales_ID int NOT NULL,
     Vehicle_ID int NOT NULL,
     Customer_ID int NOT NULL,
     Date_of_sale VARCHAR(255) NOT NULL,
     Sale_Type VARCHAR(255) NOT NULL,
     PRIMARY KEY (Sale_ID),
     FOREIGN KEY (Vehicle_ID) REFERENCES Vehicles(Vehicle_ID),
     FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID));

Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — sticky bit, Apr 26 '19 at 23:26
@stickybit - with the edit, I don't think it's a duplicate. Quotes are not part of the issue as asked. — Edward Barnard, Apr 26 '19 at 23:31
It's generally best for a foreign key to reference the primary key of the other table, not some other column. — Barmar, Apr 26 '19 at 23:33
@EdwardBarnard I think he linked to that because you have to use backticks when column names contain spaces. But that's just one of the several problems in this code. — Barmar, Apr 26 '19 at 23:35
@barmar I've edited to change this so it is now correct with the _ in the spaces. — Harry Stanford, Apr 26 '19 at 23:37
@EdwardBarnard: Well, they use identifiers with space, that needed backticks. But OK then there's also `Model` vs `Model ID` or `Model_ID`, so it's not the only problem and I retract the close vote. — sticky bit, Apr 26 '19 at 23:38

score 0 · Answer 1 · answered Apr 26 '19 at 23:29

0

Your SQL has spaces as part of the identifier, which is not allowed. For example (Customer ID) rather than (Customer_ID). That's true throughout your example code. Also, when working with foreign keys, be sure you have the same type, including signed/unsigned. If you are using utf8mb4, a varchar index is limited to 191 rather than 255, and you'll want your field referenced with a foreign key to also be an index. (This advice MIGHT be obsolete, depending on your version, but the spaces are a problem.)

answered Apr 26 '19 at 23:29

Edward Barnard

346
3
17

1

Spaces are allowed, but you have to put backticks around the name, so they're generally not a good idea. But he's not consistent, he has `Model_ID` as the column name, but `PRIMARY KEY(Model ID)`. They have to be the same. – Barmar Apr 26 '19 at 23:34
1

I've edited to change this so it is now correct with the _ in the spaces. – Harry Stanford Apr 26 '19 at 23:37

Barmar · Answer 2 · 2019-04-26T23:57:37.217

0

The column you reference has to have an index, but there's no index on the Model_name column in Models. You need to add:

INDEX (Model_name)

CREATE TABLE Models (
     Model_ID int NOT NULL,
     Manufacturer VARCHAR(255) NOT NULL,
     Model_name VARCHAR(255) NOT NULL,
     Wheel_Drive_Type VARCHAR(255) NOT NULL,
     PRIMARY KEY (Model_ID)
     INDEX (Model_name));

I suggest you take the Model_name column out of Vehicles, and replace it with Model_ID. It's usually best to have foreign keys point to primary keys.

edited Apr 26 '19 at 23:57

answered Apr 26 '19 at 23:43

Barmar

741,623
53
500
612

The model column has now been suitably renamed to Model_name - none of the instances of foreign keys work though? – Harry Stanford Apr 26 '19 at 23:47
Since the other foreign keys reference `Vehicles`, they'll get an error if you can't create this table. – Barmar Apr 26 '19 at 23:59

Gro · Answer 3 · 2019-04-27T11:19:25.693

The problem seems to be with this segment FOREIGN KEY (Model) REFERENCES Models(Model));

Model_ID is the primary key of your table Models, hence the statement segment should be FOREIGN KEY (Model) REFERENCES Models(Model_ID));

Also, the foreign key should be the primary key of the reference table with the same data type. Your DDL for Vehicles should look like following

CREATE TABLE Vehicles (
    Vehicle_ID int, 
    Vehicle_Type VARCHAR(255), 
    Model_ID int, 
    Engine_Size float, 
    Condition VARCHAR(255), 
    Price float, 
    PRIMARY KEY (Vehicle ID), 
    FOREIGN KEY (Model_ID) REFERENCES Models(Model_ID)
);

How to fix foreign key errors in MySQL?

3 Answers3