错误1005 (HY000)：无法创建表“CBDB.Subsystem”(errno: 150)

Question

当试图在mariadb中创建表"Subsystem“时，我一直收到一个错误。我能够在mysql中成功地运行这个程序，但是当尝试使用mariadb时，我会得到一些错误，我猜这些错误与语法有关，或者有些地方我完全忽略了外键。下面是我首先创建的表，试图先创建父表.

-- Creating Tables 
DROP TABLE IF EXISTS Storage_Type;
CREATE TABLE Storage_Type (
stor_model VARCHAR(10) NOT NULL,
stor_class VARCHAR(12) NOT NULL,
stor_name VARCHAR(15) NOT NULL,
CONSTRAINT pk_stor_model PRIMARY KEY (stor_model)
);

DROP TABLE IF EXISTS Service_Level;
CREATE TABLE Service_Level (
serv_level CHAR(2) NOT NULL,
serv_name VARCHAR(30),
serv_maxiops VARCHAR(7),
CONSTRAINT pk_serv_level PRIMARY KEY (serv_level)
);

DROP TABLE IF EXISTS Site;
CREATE TABLE Site (
site_num VARCHAR(10) NOT NULL,
site_name VARCHAR(15) NOT NULL,
site_address VARCHAR(30) NOT NULL,
site_suburb VARCHAR(20) NOT NULL,
site_city VARCHAR(15) NOT NULL,
site_pcode CHAR(4) NOT NULL,
CONSTRAINT pk_site_num PRIMARY KEY (site_num)
);

---No sure what i'm doing wrong on this table??
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer (
site_num VARCHAR(10) NOT NULL,
cust_name VARCHAR(20) NOT NULL,
site_description VARCHAR(50) NOT NULL,
CONSTRAINT pk_site_num PRIMARY KEY (site_num),
CONSTRAINT fk_site_num FOREIGN KEY (site_num) REFERENCES Site(site_num)
);

DROP TABLE IF EXISTS Pools;
CREATE TABLE Pools (
sub_serial MEDIUMINT NOT NULL,
pool_id CHAR(3) NOT NULL,
data_date DATE NOT NULL,
pool_name VARCHAR(30),
pool_type CHAR(4),
pool_totalcapacity INT NOT NULL,
pool_usedcapacity INT NOT NULL,
pool_usedpercent INT,
pool_availablecapacity INT NOT NULL,
pool_subscribedcapacity INT,
pool_subscriptionpercent INT,
serv_level CHAR(2),
CONSTRAINT pk_pool PRIMARY KEY (sub_serial, pool_id, data_date),
CONSTRAINT fk_serv_level FOREIGN KEY (serv_level) REFERENCES Service_Level(serv_level)
);

DROP TABLE IF EXISTS Costs;
CREATE TABLE Costs (
cost_date DATE NOT NULL,
serv_level CHAR(2),
cost_gbmonth VARCHAR(10),
cost_gbday VARCHAR(10),
CONSTRAINT pk_cost PRIMARY KEY (cost_date,serv_level),
CONSTRAINT fk_cost FOREIGN KEY (serv_level) REFERENCES Service_Level(serv_level)
);

--------ISSUES creating this table.....
DROP TABLE IF EXISTS Subsystem;
CREATE TABLE Subsystem (
sub_serial MEDIUMINT NOT NULL,
sub_name VARCHAR(10) NOT NULL,
stor_model VARCHAR(10) NOT NULL,
cust_name VARCHAR(50) UNIQUE,
CONSTRAINT pk_subsys PRIMARY KEY (sub_serial),
CONSTRAINT fk_subsys_stor_model FOREIGN KEY (stor_model) REFERENCES Storage_Type(stor_model),
CONSTRAINT fk_subsys_cust_name FOREIGN KEY (cust_name) REFERENCES Customer(cust_name)
);

错误：

ERROR 1005 (HY000): Can't create table 'CBDB.Subsystem' (errno: 150)

我运行了一个显示引擎INNODB状态，并得到以下信息，但我似乎无法确定问题所在。

> ------------------------ LATEST FOREIGN KEY ERROR
> ------------------------ 171124 16:09:31 Error in foreign key constraint of table `CBDB`.`Subsystem`:  FOREIGN KEY (cust_name)
> REFERENCES Customer(cust_name) ): 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. 

关于我哪里出了问题有什么建议或想法吗？

干杯

Answer 1

一段时间后，查看“显示引擎无害数据库状态”的详细信息，并将其引用到mariadb错误代码。他们指出，在Customer表中缺少一个索引，因为主键是来自另一个表的引用值。我通过以下方式创建了一个索引：

create index idx_cust on Customer(cust_name);

但是，在创建表之后，在创建表时要使用语法。通过创建索引，我能够正确引用外键。希望这能帮助其他人处理稍微模糊的错误信息。