MySQL“错误1005(HY000):无法创建表'foo。#sql-12c_4'(errno:150)” [英] MySQL "ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)"
问题描述
我正在数据库 foo
中创建一些表格,但是每次我最终用errno 150关于外键。首先,这里是我创建表的代码:
pre $ codeREATE TABLE客户端
(
client_id CHAR(10) NOT NULL,
client_name CHAR(50)NOT NULL,
temporary_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应用程序
(
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教师
(
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
(
)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
);
办公室
(
)办公室空间$ NOT $ $ $ $ $ $ $ $ $ $ $ $ $ $办公地址$ 50)NULL,
office_zip CHAR(10)NULL,
office_phone INT NULL,
office_email CHAR(255)NULL
);
CREATE TABLE课程
(
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 ,
分数INT NULL,
test_notes CHAR(255)NULL
);
ALTER TABLE客户端添加主键(client_id);
ALTER TABLE应用程序ADD PRIMARY KEY(application_id);
ALTER TABLE Instructors ADD PRIMARY KEY(instructor_id);
ALTER TABLE办公室ADD PRIMARY KEY(office_id);
ALTER TABLE课程ADD PRIMARY KEY(lesson_num);
ALTER TABLE DrivingTests ADD PRIMARY KEY(test_num);
ALTER TABLE应用程序ADD CONSTRAINT FK_Applications_Offices FOREIGN KEY(office_id)参考办公室(office_id);
ALTER TABLE应用程序ADD CONSTRAINT FK_Applications_Clients FOREIGN KEY(client_id)REFERENCES客户端(client_id);
ALTER TABLE应用程序ADD CONSTRAINT FK_Applications_Instructors FOREIGN KEY(instructor_id)参考指南(instructor_id);
ALTER TABLE应用程序ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY(car_id)参考Cars(car_id);
ALTER TABLE课程ADD CONSTRAINT FK_Lessons_Clients FOREIGN KEY(client_id)REFERENCES客户端(client_id);
ALTER TABLE汽车ADD CONSTRAINT FK_Cars_Offices FOREIGN KEY(office_id)参考办公室(office_id);
ALTER TABLE客户端添加约束FK_DrivingTests_Clients FOREIGN KEY(client_id)REFERENCES客户端(client_id);
这些是我得到的错误:
的MySQL> ALTER TABLE应用程序ADD CONSTRAINT FK_Applications_Cars FOREIGN KEY
(car_id)参考Cars(car_id);
错误1005(HY000):无法创建表'foo。#sql-12c_4'(errno:150)
我运行了显示引擎INNODB状态
,给出了更详细的错误描述:
------------------------
最新的外键错误
------ ------------------
100509 20:59:49表foo /#sql-12c_4的外键约束出错:
FOREIGN KEY(car_id )参考Cars(car_id):
在引用的表中找不到索引,其中引用
的列作为第一列出现,或表中的列类型
与引用的表不匹配为了约束。
请注意,在> = InnoDB-4.1.12创建的
表中,ENUM和SET的内部存储类型发生了变化,旧表
中的这样的列不能被新的表。
有关正确的外键定义,请参阅http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
。
------------
我在StackOverflow和其他在线 - 在这里找到了一个有用的博客文章,指导如何解决这个错误 - 但我无法弄清楚发生了什么问题。任何帮助将不胜感激!
您应该使car_id成为汽车的主键。
I was working on creating some tables in database foo
, but every time I end up with errno 150 regarding the foreign key. Firstly, here's my code for creating tables:
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);
These are the errors that I get:
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)
I ran SHOW ENGINE INNODB STATUS
which gives a more detailed error description:
------------------------
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.
------------
I searched around on StackOverflow and elsewhere online - came across a helpful blog post here with pointers on how to resolve this error - but I can't figure out what's going wrong. Any help would be appreciated!
You should make car_id a primary key in cars.
这篇关于MySQL“错误1005(HY000):无法创建表'foo。#sql-12c_4'(errno:150)”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!