I hope I did not get too long in this matter, I just want to make sure that what I ask is completely clear (I think this is rather confusing :).
I have a database with a bunch of tables with all the foreign key restrictions. Relationships sometimes represent multiple tables, as well as cases where a child element is associated with multiple parent tables. I want to insert a copy of one of the rows of the “top-level” table and all the child table data related to it (while maintaining relational integrity). That is, my new top-level row gets its own primary key (via auto_increment), and all new child rows get its own primary keys (again through auto_increment), and all the information in the foreign key of the tables is similar to the data that I copied (only now with newly created primary keys). So now I will have a copy of the relational data that has been independently modified from the original.
To make my example more concrete, I carefully set up a similar, but simpler example. Allows you to define the following tables:
alt text http://www.freeimagehosting.net/uploads/ef22070a89.png
All green id fields are the primary keys of auto_update, and yellow are indexed columns with foreign key constraints. Suppose a database first contains the following data:
job_types
+----+----------+
| id | jobcode |
+----+----------+
| 1 | DEADBEEF |
| 3 | FEEDFACE |
+----+----------+
managers
+----+---------------+-------------+
| id | name | job_type_id |
+----+---------------+-------------+
| 1 | John | 1 |
| 3 | Michael Scott | 3 |
+----+---------------+-------------+
departments
+----+------+------------+
| id | name | manager_id |
+----+------+------------+
| 1 | H32 | 1 |
| 2 | X11 | 3 |
+----+------+------------+
employees
+----+-------------+---------------+------------+-------------+
| id | name | department_id | manager_id | job_type_id |
+----+-------------+---------------+------------+-------------+
| 1 | Billy Bob | 1 | 1 | 1 |
| 2 | Sandra Lee | 1 | 1 | 3 |
| 3 | Buddy Holly | 2 | 3 | 1 |
+----+-------------+---------------+------------+-------------+
Now tell me what I want to do is make a relational copy of the H32 department (id = 1).
What I have to finish is something like the following (obviously, the actual values of the primary keys are not important, there is referential integrity).
job_types
+----+----------+
| id | jobcode |
+----+----------+
| 1 | DEADBEEF |
| 3 | FEEDFACE |
| 4 | DEADBEEF |
| 5 | FEEDFACE |
+----+----------+
managers
+----+---------------+-------------+
| id | name | job_type_id |
+----+---------------+-------------+
| 1 | John | 1 |
| 3 | Michael Scott | 3 |
| 4 | John | 4 |
+----+---------------+-------------+
departments
+----+------+------------+
| id | name | manager_id |
+----+------+------------+
| 1 | H32 | 1 |
| 2 | X11 | 3 |
| 3 | H32 | 4 |
+----+------+------------+
employees
+----+-------------+---------------+------------+-------------+
| id | name | department_id | manager_id | job_type_id |
+----+-------------+---------------+------------+-------------+
| 1 | Billy Bob | 1 | 1 | 1 |
| 2 | Sandra Lee | 1 | 1 | 3 |
| 3 | Buddy Holly | 2 | 3 | 1 |
| 4 | Billy Bob | 3 | 4 | 4 |
| 5 | Sandra Lee | 3 | 4 | 5 |
+----+-------------+---------------+------------+-------------+
? , MySQL, InnoDB, Grails. , - "".
- ,
MySQLDump PasteBin.
, / , , , " "...