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.
I actually got here after searching for a similar answer. It looks like REPLACE INTO still deletes items as long as you have ON DELETE CASCADE set for your constraint.
See http://www.mysqlperformanceblog.com/2007/01/18/insert-on-duplicate-key-update-and-replace-into/
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.
