Marko Mäkelä added a comment - 2024-11-22 08:20 dicode , if I understood this correctly, you are intentionally using such a schema and indeed prefer to have a DML time error instead of a DDL time error. I agree that we should revise this. One possible fix could be that if sql_mode does not include STRICT_ALL_TABLES or STRICT_TRANS_TABLES , we would ignore any ON UPDATE inconsistency that is related to the referencing column being declared as NOT NULL . When it comes to ON UPDATE SET NULL , I can imagine that someone might want to (ab)use them as a kind of a stricter FOREIGN KEY constraint, not allowing any change in the parent table as long as a referencing record in a child table (with NOT NULL on that column) exists. I do not see any reason to allow ON DELETE SET NULL when the referencing column is declared NOT NULL . The same effect (prohibiting DELETE of the referenced row if a referencing row exists) would be achieved without defining any ON DELETE SET NULL . I tested the following variant of your example: --source include/have_innodb.inc   CREATE TABLE fg ( fg_id int unsigned AUTO_INCREMENT PRIMARY KEY , fg_p_id int unsigned REFERENCES fg (fg_id) ) ENGINE=InnoDB;   insert into fg values (1, null ),(2,1),(3, null ),(4,3),(5,1);   CREATE TABLE f (f_id int unsigned AUTO_INCREMENT PRIMARY KEY ) ENGINE=InnoDB;   insert into f values (1);   CREATE TABLE f_fg ( f_id int unsigned NOT NULL REFERENCES f(f_id) ON DELETE CASCADE ON UPDATE CASCADE , fg_p_id int unsigned, fg_id int unsigned, fg int unsigned NOT NULL , FOREIGN KEY (fg_p_id, fg_id) REFERENCES fg (fg_p_id, fg_id) ON UPDATE CASCADE ) ENGINE=InnoDB;   insert into f_fg values (1,1,2,1),(1,1,5,5),(1,3,4,4),(1,3, null ,0);   update fg set fg_p_id= null where fg_id=4; select * from f_fg; drop table f_fg,f,fg; As far as I understand, you would want the original DDL to go through and the UPDATE statement to fail due to a foreign key constraint violation, because the record (1,3,4,4) cannot be updated to (1,NULL,4,4), since f_fg.fg_p_id is declared NOT NULL . Above, I "fixed" the DDL by removing the NOT NULL and by omitting a PRIMARY KEY in f_fg (all PRIMARY KEY columns are implicitly NOT NULL ). Note: Omitting a PRIMARY KEY is a bad idea if you are using any replication; see MDEV-21181 . InnoDB would add a hidden internal 48-bit column DB_ROW_ID that acts as the primary key, but it would not be visible outside InnoDB. It would be better to add an AUTO_INCREMENT PRIMARY KEY if one wanted to go this route.

Kristian Nielsen added a comment - 2024-11-22 08:43 I am wondering why there is no reference to the SQL standard about what is the prescribed behaviour there. Or is this not covered by the standard? How does it work in other databases? I do agree that to the extend that this concerns regressions in GA releases, this needs to be taken into account as well as the standard, but it still seems necessary to have a good understanding of what the standard says (if anything), and how things work in other databases. Hope this helps, - Kristian.

Tim Westervoorde added a comment - 2024-11-22 09:29 markus you are right about my intentions, we explicitly use this kind of foreign key to protect the data against modifications which would result in a broken tree. Your mention about the primary key is why we use a "shadow" column `fg` which hasn't got a foreign key and mirrors fg_id to allow binding a f to a parent (fg_p_id) without selecting a child yet (fgid). I can live with the solution of allowing the DDL only when sql_mode does not include STRICT_ALL_TABLES or STRICT_TRANS_TABLES.

Marko Mäkelä added a comment - 2024-11-22 09:46 knielsen , I don’t have access to the standard. The services https://dbfiddle.dev and https://dbfiddle.uk could be used for checking how some other RDBMS would behave.

Marko Mäkelä added a comment - 2025-01-21 06:07 If I understood the test case correctly, the fix in its current form would not consistently allow ON UPDATE…SET NULL in non-strict sql_mode when the referencing column is declared NOT NULL . I’m glad to see that the addition to our regression test suite demonstrates that all the DML time checks are already in place.