I have this code:

CREATE TABLE CarDrivers (
  driver_name VARCHAR (50) UNIQUE
);

INSERT INTO CarDrivers (driver_name) VALUES ("John"), ("Jarvis");

CREATE TABLE Cars (
  driver_name VARCHAR (50) DEFAULT "John",
  -- driver_names are connected
  FOREIGN KEY (driver_name) REFERENCES CarDrivers (driver_name)
  -- but we can always return to value "John", which is present in CarDrivers
  ON UPDATE SET DEFAULT
);

INSERT INTO Cars SET driver_name = 'Jarvis';

-- this is where the error happens
UPDATE CarDrivers SET driver_name = 'Jarvease' WHERE driver_name = 'Jarvis';

The code results in an error:

Cannot delete or update a parent row. A foreign key constraint fails: ....Cars, CONSTRAINT Cars_ibfk_1 FOREIGN KEY (driver_name) REFERENCES CarDrivers (driver_name)

Why does this error occur when we've told to Cars table that ON UPDATE of CarDrivers.driver_name Cars.driver_name should be set to DEFAULT "John", which is a value of CarDrivers.driver_name?

By the way, constraints ... ON UPDATE SET NULL ... ON UPDATE CASCADE work properly and allow us update CarDrivers.driver_name!

Thank you for your time!

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html says:

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

In other words, this feature is not working in the current version of MySQL.