Database issues errors not able to fix it

Question

I am trying to crate a set of tables for my schema and I have used the following code. But I'm getting an error. I'm new to mysql and any help would be appreciated.

create table AutoSeller;
    create table County(
        CountyID INT,
        CountyName varchar(25),
        PRIMARY KEY (CountyID)
        );

create table City (
    CityID int,
    CityName varchar(50),
    CountyID int,
    FOREIGN KEY (CountyID) REFERENCES county(CountyID),
    PRIMARY KEY (CityID)
    );

 CREATE table Postcode(
    PostcodeID int,
    Postcode varchar(8),
    CityID int,
    FOREIGN KEY(CityID) REFERENCES city(CityID),
    PRIMARY KEY (PostcodeID)
    );

create table Buyer(
    BuyerID int,
    BuyerFN varchar(15),
    BuyerLN varchar(15),
    CountyID int,
    CityID int,
    Address varchar(30),
    PostcodeID int,
    ContactNr varchar(15),
    FOREIGN KEY (PostcodeID) REFERENCES postcode (PostcodeID),
    FOREIGN KEY (CityID) REFERENCES city (CityID),
    FOREIGN KEY (CountyID) REFERENCES county (CountyID),
    PRIMARY KEY (BuyerID)
    );

create table Seller(
    SellerID int,
    SellerFN varchar(15),
    SellerLN varchar(15),
    CountyID int,
    CityID int,
    Address varchar(30),
    PostcodeID int,
    ContactNr varchar(15),
    FOREIGN KEY (PostcodeID) REFERENCES postcode (PostcodeID),
    FOREIGN KEY (CityID) REFERENCES city (CityID),
    FOREIGN KEY (CountyID) REFERENCES county (CountyID),
    PRIMARY KEY (SellerID)
    );

create table Make(
    CarMakeID int,
    CarMake varchar(15),
    PRIMARY KEY (CarMakeID)
    );

create table  Colour(
    CarColourID int,
    CarColour varchar(10),
    PRIMARY KEY (CarColour)
    );

create TABLE Fuel_type(
    CarFuelID int,
    CarFuel varchar(10),
    PRIMARY KEY (CarFuelID)
    );

create table Body(
    CarBodyID int,
    CarBody varchar (15),
    PRIMARY KEY (CarBodyID)
    );

CREATE TABLE car(
    CarID int,
    CarReg varchar(10),
    CarMakeID int,
    CarModel varchar(15),
    CarColourID int,
    CarBodyID int,
    CarFuelID int,
    CarAge int,
    CarEngine int,
    CarMileage int,
    BuyerID int,
    SellerID int,
    PRIMARY KEY (CarID),
    FOREIGN KEY (CarMakeID) REFERENCES make (CarMakeID),
    FOREIGN KEY (CarColourID) REFERENCES colour (CarColourID),
    FOREIGN KEY (CarFuelID) REFERENCES fuel_type (CarFuelID),
    FOREIGN KEY (BuyerID) REFERENCES buyer (BuyerID),
    FOREIGN KEY (SellerID) REFERENCES seller (SellerID)
    );

I just start learning mySql and I am struggling with this error.

1005 - Can't create table autoseller.car (errno: 150 "Foreign key constraint is incorrectly formed")

Can somebody help me?

Answer 1

This is the problem:

create table  Colour(
    CarColourID int,
    CarColour varchar(10),
    PRIMARY KEY (CarColour)
    );

The primary key should be CarColourID, not CarColour. The error you're getting is because the column referenced in a foreign key has to be indexed. Since you didn't make CarColourID the primary key, it doesn't have an index.