Foreign Keys, Referential Actions: Misleading information about `SET DEFAULT`? (Is actually accepted, but just runs default referential action)
Before I open a bug report, I thought to maybe double check with you here to see if you agree with my observations that the MySQL 8 documentation of the "Referential Actions" section on the "Foreign Keys" page (https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html) is wrong:
> 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.
I don't think this is correct. The table definition is accepted just fine and the table is created as with any other referential action.
But, I get an error when I run a query that would trigger this referential action and the field is definitely not updated to the default. But the error is also not a special error that tells me that SET DEFAULT is not supported, but it looks to me like this is just the same error as when the default referential action, NO ACTION, is executed.
Here is the SQL to reproduce this:
```
CREATE TABLE `User` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- CreateTable
CREATE TABLE `Post` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`userId` INTEGER NULL DEFAULT 3,
PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- AddForeignKey
ALTER TABLE `Post` ADD CONSTRAINT `Post_userId_fkey`
FOREIGN KEY (`userId`) REFERENCES `User`(`id`)
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
INSERT INTO `User`(`id`) VALUES (1);
INSERT INTO `User`(`id`) VALUES (3);
INSERT INTO `Post`(`id`, `userId`) VALUES (1, 1);
UPDATE `User` SET `id` = 2 WHERE `id` = 1;
SELECT `id`, `userId`* FROM `Post`;
UPDATE `Post` SET `userId` = 70 WHERE `userId` = 1;
```
This was different in MySQL 5.6 and 5.7 where the `ALTER TABLE` indeed already failed.
Do you agree that the documentation of MySQL 8 was not updated to reflect this changing behavior?
J
Subject
Views
Written By
Posted
Foreign Keys, Referential Actions: Misleading information about `SET DEFAULT`? (Is actually accepted, but just runs default referential action)
618
November 04, 2022 05:35PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.