1005-无法创建表
我正在创建一个SQL脚本并将其导入phpMyAdmin。这是代码:
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';
我收到的是错误报告:
错误SQL查询:
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表示:文档
1005-无法创建表carpool
。driver
(错误号:150"外键约束格式不正确")(详细信息…)
我觉得我正确地引用了表中的外键,但我尝试了一些小的更改,但都没有成功。
carpool
表引用了startinglocation
和endinglocation
表,因此需要在它们之后创建。
只需更改创建顺序,在脚本开始时执行startinglocation
和endinglocation
表,就可以了。
@Paul Spiegel给出了问题第二部分的答案:driver
和passenger
表引用了不同类型的users.username
。