Bug #92028 Implement a way to validate values in foreign keys
Submitted: 15 Aug 2018 19:44 Modified: 20 Oct 2021 21:00
Reporter: Federico Razzoli (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[15 Aug 2018 19:44] Federico Razzoli 
Description:
The foreign_key_checks variable is probably meant to allow temporary foreign key violations. Since MySQL doesn't allow to postpone constraint checks at the end of a transaction, this is very useful. However, humans make mistakes, so it's easy to leave inconsistent values in tables.

Please implement a way to validate the foreign key in a table. It would be nice to get information about all violations with a single statement, so we can fix them.

How to repeat:
CREATE TABLE t1 (
    id INT UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE t2 (
    id INT UNSIGNED AUTO_INCREMENT,
    PRIMARY KEY (id),
    FOREIGN KEY fk_t2_t1 (id)
        REFERENCES t1 (id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

SET SESSION foreign_key_checks = 0;

INSERT INTO t2 VALUES (1);
[20 Oct 2021 21:00] MySQL Verification Team 
Thank you for the feature request.