javanewb2.0 javanewb2.0 - 7 months ago 26
SQL Question

SQL Script Errors in phpMyAdamin - Foreign Key Restraint

I am creating a SQL script and importing it into phpMyAdmin. Here is the 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';


And I am receiving this as the error report:

Error
SQL query:

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
)


MySQL said: Documentation

1005 - Can't create table
carpool
.
driver
(errno: 150 "Foreign key constraint is incorrectly formed") (Details…)



I feel like I referenced the tables correctly for the foreign keys, but have been unsuccessful with minor changes I have tried.

Answer

The carpool table is referencing the startinglocation and endinglocation tables, so it needs to be created after them.

Simply change the creation order, doing startinglocation and endinglocation tables at the beginning of your script, and it should work.

@Paul Spiegel gave you the answer to the second part of your question: driver and passenger tables are referencing users.username with a different type.

Comments