MySQL执行SQL报错1005_外键-MySQL“错误1005（HY000）：无法创建表'foo。＃sql-12c_4'（errno：150）”...

我当时正在数据库SHOW ENGINE INNODB STATUS中创建一些表，但是每次我以外键errno 150结尾。 首先，这是我创建表的代码：

CREATE TABLE Clients

(

client_id CHAR(10) NOT NULL ,

client_name CHAR(50) NOT NULL ,

provisional_license_num CHAR(50) NOT NULL ,

client_address CHAR(50) NULL ,

client_city CHAR(50) NULL ,

client_county CHAR(50) NULL ,

client_zip CHAR(10) NULL ,

client_phone INT NULL ,

client_email CHAR(255) NULL ,

client_dob DATETIME NULL ,

test_attempts INT NULL

);

CREATE TABLE Applications

(

application_id CHAR(10) NOT NULL ,

office_id INT NOT NULL ,

client_id CHAR(10) NOT NULL ,

instructor_id CHAR(10) NOT NULL ,

car_id CHAR(10) NOT NULL ,

application_date DATETIME NULL

);

CREATE TABLE Instructors

(

instructor_id CHAR(10) NOT NULL ,

office_id INT NOT NULL ,

instructor_name CHAR(50) NOT NULL ,

instructor_address CHAR(50) NULL ,

instructor_city CHAR(50) NULL ,

instructor_county CHAR(50) NULL ,

instructor_zip CHAR(10) NULL ,

instructor_phone INT NULL ,

instructor_email CHAR(255) NULL ,

instructor_dob DATETIME NULL ,

lessons_given INT NULL

);

CREATE TABLE Cars

(

car_id CHAR(10) NOT NULL ,

office_id INT NOT NULL ,

engine_serial_num CHAR(10) NULL ,

registration_num CHAR(10) NULL ,

car_make CHAR(50) NULL ,

car_model CHAR(50) NULL

);

CREATE TABLE Offices

(

office_id INT NOT NULL ,

office_address CHAR(50) NULL ,

office_city CHAR(50) NULL ,

office_County CHAR(50) NULL ,

office_zip CHAR(10) NULL ,

office_phone INT NULL ,

office_email CHAR(255) NULL

);

CREATE TABLE Lessons

(

lesson_num INT NOT NULL ,

client_id CHAR(10) NOT NULL ,

date DATETIME NOT NULL ,

time DATETIME NOT NULL ,

milegage_used DECIMAL(5, 2) NULL ,

progress CHAR(50) NULL

);

CREATE TABLE DrivingTests

(

test_num INT NOT NULL ,

client_id CHAR(10) NOT NULL ,

test_date DATETIME NOT NULL ,

seat_num INT NOT NULL ,

score INT NULL ,

test_notes CHAR(255) NULL

);

ALTER TABLE Clients ADD PRIMARY KEY (client_id);

ALTER TABLE Applications ADD PRIMARY KEY (application_id);

ALTER TABLE Instructors ADD PRIMARY KEY (instructor_id);

ALTER TABLE Offices ADD PRIMARY KEY (office_id);

ALTER TABLE Lessons ADD PRIMARY KEY (lesson_num);

ALTER TABLE DrivingTests ADD PRIMARY KEY (test_num);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Offices FOREIGN KEY (office_id) REFERENCES Offices (office_id);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Instructors FOREIGN KEY (instructor_id) REFERENCES Instructors (instructor_id);

ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY (car_id) REFERENCES Cars (car_id);

ALTER TABLE Lessons ADD CONSTRAINT FK_Lessons_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);

ALTER TABLE Cars ADD CONSTRAINT FK_Cars_Offices FOREIGN KEY (office_id) REFERENCES Offices (office_id);

ALTER TABLE Clients ADD CONSTRAINT FK_DrivingTests_Clients FOREIGN KEY (client_id) REFERENCES Clients (client_id);

这些是我得到的错误：

mysql> ALTER TABLE Applications ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY

(car_id) REFERENCES Cars (car_id);

ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)

我运行SHOW ENGINE INNODB STATUS，它给出了更详细的错误描述：

------------------------

LATEST FOREIGN KEY ERROR

------------------------

100509 20:59:49 Error in foreign key constraint of table foo/#sql-12c_4:

FOREIGN KEY (car_id) REFERENCES Cars (car_id):

Cannot find an index in the referenced table where the

referenced columns appear as the first columns, or column types

in the table and the referenced table do not match for constraint.

Note that the internal storage type of ENUM and SET changed in

tables created with >= InnoDB-4.1.12, and such columns in old tables

cannot be referenced by such columns in new tables.

See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

for correct foreign key definition.

------------

我在StackOverflow和其他在线位置上进行了搜索-在这里找到了一篇有用的博客文章，其中提供了有关如何解决此错误的指针-但我不知道出了什么问题。 任何帮助，将不胜感激！