MySQL: ON UPDATE SET DEFAULT raises an error when it seems like it shouldn't

Question

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!

@Akina After your comment I've tried this: `UPDATE CarDrivers SET driver_name = 'Jarvease' WHERE driver_name = 'Jarvis' DEFAULT 'John'`. And it raised an error due to the addition. So, what do you mean? — Daniil, Apr 18 '22 at 13:24

score 1 · Accepted Answer · answered Apr 18 '22 at 14:19

1

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.

answered Apr 18 '22 at 14:19

Bill Karwin

538,548
86
673
828

Wow! That is a very tricky moment! Thank you for telling this! I changed *Cars* definition to `... ENGINE = MyISAM` and it worked! Thoughts aloud: *Is it a tendency that MySQL server uses *InnoDB* by default?* – Daniil Apr 18 '22 at 14:29
1

If you use MyISAM, then foreign keys are not supported at all. If you `SHOW CREATE TABLE Cars` you won't see the foreign key. It has been forgotten. You won't get the error from your test, when you update `CarDrivers`, but the row in `Cars` will not update to its default. – Bill Karwin Apr 18 '22 at 14:32
1

As for the default storage engine, yes, MySQL has used InnoDB as its default storage engine since around 2010. You must use InnoDB if you want to use foreign keys. I don't recommend using MyISAM. See my answer to [MyISAM versus Innodb](https://stackoverflow.com/a/17706717/20860) for an example. – Bill Karwin Apr 18 '22 at 14:33
Bill Karwin, thank you again! I got you. Though, for some reason, `SHOW CREATE TABLE ...` raised an error. Now I see what you meant by saying that *"this feature is not working in the current version of MySQL"*. – Daniil Apr 18 '22 at 14:44
Bill Karwin, thanks for the debate link! Be sure, I'll check your reply and whole the discussion! – Daniil Apr 18 '22 at 14:46
1

Show create table should work. If your current default schema is not the one where the table resides, you'll get an error. That's the only error I can guess, unless you mistyped your table name. – Bill Karwin Apr 18 '22 at 14:46
for some reason, I cannot address to you. I write "@ Bill Karwin" with no " " between "@" and "Bill" and the addressing simply erased as I post the comment! Do you have any idea about why this can happen? – Daniil Apr 18 '22 at 14:47
Typically leave out the spaces, for example: `@BillKarwin`. But I am notified of your comments on my answer regardless. – Bill Karwin Apr 18 '22 at 14:48
Bill Karwin (see it missing in the previous comment? :D), I thought I should type `SHOW CREATE TABLE Cars (...);` when create it! Now I typed just `SHOW CREATE TABLE Cars;` and it worked! – Daniil Apr 18 '22 at 14:50
I literally copied the addressing as you wrote it. Didn't work out. – Daniil Apr 18 '22 at 14:51
1

When you have trouble with MySQL commands, try reading the [documentation](https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html). I have been using MySQL for 20+ years and I study the documentation several times per day. – Bill Karwin Apr 18 '22 at 14:53
Thank you for the advice. Before raising a question, I always try to find necessary information in the Web, particularly in the documentation (which is amazingly written!). But I wrongly assumed that such nuances aren't described in there. I just didn't realize that the documentation describes things in such a detailed way! – Daniil Apr 19 '22 at 03:10
1

The documentation lead at MySQL is fantastic. He has been in that position since 2004. He is now Director of MySQL Documentation and Curriculum, so he is probably doing more management than writing himself, but he must be a good leader and good advocate for quality. – Bill Karwin Apr 19 '22 at 03:16
Wow! Didn't know about that! Yes, he must be an excellent leader! Whole MySQL documentation team is outstanding, which we can see by how explicit and detailed at the same time the documentation is! – Daniil Apr 19 '22 at 03:48

MySQL: ON UPDATE SET DEFAULT raises an error when it seems like it shouldn't

1 Answers1