MySQL错误150 [英] MySQL Error 150
本文介绍了MySQL错误150的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建一个脚本来规范化MySQL中的另一个表.以下是我所拥有的:
I'm trying to create a script to normalize another table in MySQL. Below is what I have:
USE hw7;
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS airport_codes;
DROP TABLE IF EXISTS airport_locations;
DROP TABLE IF EXISTS airport_codenames;
SET foreign_key_checks = 1;
CREATE TABLE airport_codes(
airport_code char(3) not null,
airline_code char(2) not null,
primary key (airport_code, airline_code)
);
INSERT INTO airport_codes SELECT DISTINCT airport_code, airline_code
FROM airport_airlines;
CREATE TABLE airport_locations(
airport_code char(3) not null,
city varchar(20) not null,
state char(2) not null,
primary key (airport_code),
constraint ap_code_fk
foreign key (airport_code)
references airport_codes(airport_code)
);
INSERT INTO airport_locations SELECT DISTINCT airport_code, city, state
FROM airport_airlines;
CREATE TABLE airport_codenames(
airline_code char(2) not null,
name varchar(20) not null,
primary key (airline_code),
constraint al_code_fk
foreign key (airline_code)
references airport_codes(airline_code)
);
INSERT INTO airport_codenames SELECT DISTINCT airline_code, name
FROM airport_airlines;
此代码会导致以下错误:
This code results in this error:
无法创建表hw7.airport_codenames errno:150
Can't create table hw7.airport_codenames errno:150
推荐答案
由于airport_codes
每个airport_code
和airline_code
具有多个可能的行(作为组合键),因此其他外键不能引用该行.将FK关系移到airport_codes
,指向airport_locations
和airport_codenames
.
Since airport_codes
has multiple possible rows per airport_code
and airline_code
(as a composite key), it cannot be referenced by other foreign keys. Move the FK relationships into airport_codes
, pointing to airport_locations
and airport_codenames
.
USE hw7;
SET foreign_key_checks = 0;
DROP TABLE IF EXISTS airport_codes;
DROP TABLE IF EXISTS airport_locations;
DROP TABLE IF EXISTS airport_codenames;
SET foreign_key_checks = 1;
CREATE TABLE airport_locations(
airport_code char(3) not null,
city varchar(20) not null,
state char(2) not null,
primary key (airport_code)
);
INSERT INTO airport_locations SELECT DISTINCT airport_code, city, state
FROM airport_airlines;
CREATE TABLE airport_codenames(
airline_code char(2) not null,
name varchar(20) not null,
primary key (airline_code)
);
INSERT INTO airport_codenames SELECT DISTINCT airline_code, name
FROM airport_airlines;
/* airport_codes moved after the other 2 tables, and FKs defined here */
CREATE TABLE airport_codes(
airport_code char(3) not null,
airline_code char(2) not null,
primary key (airport_code, airline_code),
/* FK relationships are defined here, rather than in the other tables,
since the PKs for airport_code and airline_code are defined in the
other tables.
*/
constraint ap_code_fk
foreign key (airport_code)
references airport_locations (airport_code),
constraint al_code_fk
foreign key (airline_code)
references airport_codenames (airline_code)
);
INSERT INTO airport_codes SELECT DISTINCT airport_code, airline_code
FROM airport_airlines;
这篇关于MySQL错误150的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文