mysql - 如何在 MariaDB 10.1.37/Ver 15.1 中进行适当的约束？

Question

我正在使用 MariaDB，但在引入 CONSTRAINT 时遇到了麻烦。

我的 MariaDB 版本:

Ver 15.1 Distrib 10.1.37-MariaDB

我的错误消息:

ERROR 1005 (HY000): Can't create table `schedulingGUI`.`#sql-1043_1a` (errno: 150 "Foreign key constraint is incorrectly formed")

我的城市表格条目:

CREATE TABLE city
(
  cityId INT unsigned NOT NULL AUTO_INCREMENT,
  city VARCHAR(50),
  countryId INT unsigned,
  customerName VARCHAR(50),
  address VARCHAR(50),
  postalCode VARCHAR(50),
  phone VARCHAR(50),
  createDate VARCHAR(50),
  createdBy VARCHAR(50),
  lastUpdateBy VARCHAR(50),
  PRIMARY KEY (cityId)
);

我的客户表格条目:

CREATE TABLE customer
(
  customerId INT unsigned NOT NULL AUTO_INCREMENT,
  customerName VARCHAR(50),
  addressId INT unsigned,
  active INT unsigned,
  address VARCHAR(50),
  city VARCHAR(50),
  postalCode VARCHAR(50),
  phone VARCHAR(50),
  createDate VARCHAR(50),
  createdBy VARCHAR(50),
  lastUpdateBy VARCHAR(50),
  PRIMARY KEY (customerId)
);

我的约束条目:

ALTER TABLE city
  ADD CONSTRAINT customerNameChange01
  FOREIGN KEY (customerName)
  REFERENCES customer (customerName)
  ON UPDATE CASCADE
  ON DELETE CASCADE;

我最近偶然发现了 SHOW ENGINE INNODB STATUS。它指出以下内容:

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.

感谢您的见解。

编辑:已添加内容。更正了错误消息。

Answer 1

以下工作有效:

CREATE INDEX CustomerName ON customer (customerName);

显示引擎 INNODB 状态；帮助很大。