Search code examples
mysqlreplaceinnodbcascadeonupdate

Will MySQL InnoDB ON UPDATE CASCADE constraints update properly during a MySQL REPLACE command?

If I have table B with foreign key references to table A (set to ON UPDATE CASCADE) and I run a 

LOAD DATA INFILE file.txt REPLACE INTO TABLE A;

command, will the references update properly?

Please note that I'm not talking about ON DELETE CASCADE; I know a REPLACE command will delete records in table B if I have that set.

Solution

  • MySQL doesn't fire the update event as a result of a replace query, only the delete. Here's why:

    REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

    (From the MySQL 5.0 Reference Manual)

    I have a foreign key set with ON UPDATE CASCADE ON DELETE SET NULL, and whenever I do a REPLACE to the foreign table's primary key, the foreign key in my related table gets set to NULL.