mysql - How to use "ON UPDATE CASCADE" Correctly in MariaDB 10.1.37 / Ver 15.1?

Question

I am experiencing trouble getting ON UPDATE CASCADE to work with a CONSTRAINT. If I use UPDATE to change the value of customerName in the customer table, it will not change the customerName value in the city table. No error message shows up.

The version of the MariaDB:

Ver 15.1 Distrib 10.1.37-MariaDB

My city table when using SHOW CREATE TABLE city:

city  | CREATE TABLE `city` (
  `cityId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) DEFAULT NULL,
  `countryId` int(10) unsigned DEFAULT NULL,
  `customerName` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `postalCode` varchar(50) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `createDate` varchar(50) DEFAULT NULL,
  `createdBy` varchar(50) DEFAULT NULL,
  `lastUpdateBy` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`cityId`),
  KEY `customerNameChange01` (`customerName`),
  CONSTRAINT `customerNameChange01` FOREIGN KEY (`customerName`) 
      REFERENCES `customer` (`customerName`)
      ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

My customer table SHOW CREATE TABLE customer:

customer | CREATE TABLE `customer` (
  `customerId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customerName` varchar(50) DEFAULT NULL,
  `addressId` int(10) unsigned DEFAULT NULL,
  `active` int(10) unsigned DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `postalCode` varchar(50) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `createDate` varchar(50) DEFAULT NULL,
  `createdBy` varchar(50) DEFAULT NULL,
  `lastUpdateBy` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`customerId`),
  KEY `CustomerName` (`customerName`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

These are the commands I used to create the index and CONSTRAINT:

CREATE INDEX CustomerName ON customer (customerName);

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

In the customer table, the CustomerName key references an index. Otherwise, I would not have been able to put in the CONSTRAINT in the city table.

Update: The code works fine in DB Fiddle for MariaDB 10.2 and personal testing confirms that the example code from there works in my own database as well.

Thank you for spending your time.

标签： mysqlmariadb