SQL Script Errors in phpMyAdamin - Foreign Key Restraint
<p>I am creating a SQL script and importing it into phpMyAdmin. Here is the code:</p>
<pre><code>DROP DATABASE IF EXISTS Carpool;
CREATE DATABASE Carpool;
USE Carpool;

CREATE TABLE IF NOT EXISTS users (
    username    varchar(100)    NOT NULL,
    firstname   varchar(100)    NOT NULL,
    lastname    varchar(100)    NOT NULL,
    age         int(10)         NOT NULL,
    email       varchar(100)    NOT NULL,
    gender      varchar(1)      NOT NULL,
    passhash    varchar(100)    NOT NULL,
    PRIMARY KEY(username)
);

CREATE TABLE IF NOT EXISTS cars (
    car_id      int(50)     NOT NULL AUTO_INCREMENT,
    cartype     varchar(50),
    neatness    varchar(50),
    color       varchar(50),
    seats       int(50),
PRIMARY KEY(car_id)
);

CREATE TABLE IF NOT EXISTS startinglocation (
    startinglocation_id int(100)        NOT NULL AUTO_INCREMENT,
    address             varchar(200)    NOT NULL,
    zipcode             varchar(50)     NOT NULL,
    city                varchar(50)     NOT NULL,
    state               varchar(50)     NOT NULL,
    longitude           float(10)       NOT NULL,
    latitude            float(10)       NOT NULL,
PRIMARY KEY(startinglocation_id)
);

CREATE TABLE IF NOT EXISTS endinglocation (
    endinglocation_id   int(100)        NOT NULL AUTO_INCREMENT,
    address             varchar(200)    NOT NULL,
    zipcode             varchar(50)     NOT NULL,
    city                varchar(50)     NOT NULL,
    state               varchar(50)     NOT NULL,
    longitude           float(10)       NOT NULL,
    latitude            float(10)       NOT NULL,
PRIMARY KEY(endinglocation_id)
);

CREATE TABLE IF NOT EXISTS carpool (

    car_id              int(50),
    carpool_id          int(50) NOT NULL AUTO_INCREMENT,
    datetime            varchar(50),
    duration            varchar(50),
    numberofpassengers  int(10),
    startingtime        varchar(50) NOT NULL,
    endingtime          varchar(50) NOT NULL,
    recurrencelevel     int(10),
    startinglocation_id int(100),
    endinglocation_id   int(100),
PRIMARY KEY(carpool_id),
FOREIGN KEY(car_id)
    REFERENCES cars(car_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(startinglocation_id)
    REFERENCES startinglocation(startinglocation_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(endinglocation_id)
    REFERENCES endinglocation(endinglocation_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS driver (
    username    varchar(50) NOT NULL,
    carpool_id  int(50)     NOT NULL,
FOREIGN KEY (username)
    REFERENCES users(username)
    ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (carpool_id)
    REFERENCES carpool(carpool_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS passenger (
    username        varchar(50) NOT NULL,
    carpool_id      int(50)     NOT NULL,
FOREIGN KEY (username)
    REFERENCES users(username)
    ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (carpool_id)
    REFERENCES carpool(carpool_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);
    
GRANT SELECT, INSERT, UPDATE, DELETE
ON *
TO mgs_user
IDENTIFIED BY 'Password1';
</code></pre>
<p>And I am receiving this as the error report:</p>
<p>Error
SQL query:</p>
<pre><code>CREATE TABLE IF NOT EXISTS carpool (

    car_id              int(50),
    carpool_id          int(50) NOT NULL AUTO_INCREMENT,
    datetime            varchar(50),
    duration            varchar(50),
    numberofpassengers  int(10),
    startingtime        varchar(50) NOT NULL,
    endingtime          varchar(50) NOT NULL,
    recurrencelevel     int(10),
    startinglocation_id int(100),
    endinglocation_id   int(100),
PRIMARY KEY(carpool_id),
FOREIGN KEY(car_id)
    REFERENCES cars(car_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(startinglocation_id)
    REFERENCES startinglocation(startinglocation_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY(endinglocation_id)
    REFERENCES endinglocation(endinglocation_id)
    ON DELETE SET NULL ON UPDATE CASCADE
)
</code></pre>
<p>MySQL said: Documentation</p>
<h1>1005 - Can't create table <code>carpool</code>.<code>driver</code> (errno: 150 &quot;Foreign key constraint is incorrectly formed&quot;) (Details…)</h1>
<p>I feel like I referenced the tables correctly for the foreign keys, but have been unsuccessful with minor changes I have tried.</p>