If I have a Parent and Child table in MySQL related by a foreign key, is it possible using a SQL statement to move certain rows from Parent and the related rows from Child into archive tables (e.g. Parent_Archive and Child_Archive) in an atomic manner?
+1
A:
Use transactions - their whole purpose is to make the series of SQL statements atomic.
For example (NOT very optimized - can be improved with temp table):
START TRANSACTION;
INSERT Child_Archive
SELECT DISTINCT
Child.* FROM Child, Parent
WHERE Child.FK = Parent.PK
AND Parent.something=11;
DELETE Child WHERE FK IN (
SELECT DISTINCT PK FROM Parent WHERE Parent.something=11);
INSERT Parent_Archive
SELECT DISTINCT * FROM Parent WHERE Parent.something=11;
DELETE Parent WHERE Parent.something=11;
COMMIT;
DVK
2010-06-10 23:29:26
@DVK: Assuming Parent_Archive and Child_Archive have the same FK constraints as Parent and Child, won't the first insert fail because the parent isn't there yet?
Eric J.
2010-06-11 00:14:00
@Eric J. - It will. I am unsure of why would an archive table have such a constraint at all, though (I would not put one in). If that's the design, the solution is the one I mentioned above as "better performing" - you go with creating a temp table of PKs/FKs to archive first, and then re-order queries above in the order you alluded to (insert into P_A, insert into C_A, delete C, delete P)
DVK
2010-06-11 01:45:18