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
, CONSTRAINTCars_ibfk_1
FOREIGN KEY (driver_name
) REFERENCESCarDrivers
(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.