I am using mssql but I think this is generic.
how to duplicate a row where the table could have dependencies?
ie. table B has a foreign key into tables A, B and C. table A is not a dependent table.
thanks,
Anil

which row do you want to duplicate?

and in which table is this row?

and does this table have a primary key?

Your table ought to have a key which would prevent duplicate rows. I suggest you rethink or restate your requirement to be clear about what key(s) are involved and exactly what you want to duplicate.

Sorry for not specifying more clearly.
the row is in table B. All the tables have primary keys.
by 'duplicate', I mean cloning the table row and inserting the clone in the table with another primary key, of course.
Someone proposed a solution but that involved add ing a 'parent-id' column to each table. A solution seems difficult because the foreign keys are not null. So if there are 15 tables, I dont know how.

Sorry for not specifying more clearly. Please see above.

Can you explain your real world scenario rather than "A" and "B"? I find it rather confusing to follow to be honest!

of course


that someone is misguided, to say the least

alternatively, it's possible that this someone knew more about your tables than we do

Suppose a natural disaster (flood, tornado, fires...) strikes, and farmer Brown's crops, fences, soil, buildings are damaged. So he files an application requesting financial aid to help him repair the damage. He lists the project line items or techniques (building fence, reseeding crops etc.) and gets approval for each and a percentage of his request is paid for.

So looking at a subset:

main tables:
1) aid_request
primary key: aid_request_id

2) farmer
primary key: farmer_id


linking tables:

3) project_item
primary key: project_item_id
foreign keys (not null): aid_request_id

4) farmer_project_item_estimate
primary key: farmer_project_item_estimate_id
foreign keys (not null): farmer_id, project_item_id, aid_request_id

5) farmer_aid_request
primary key: farmer_aid_request_id
foreign keys (not null): aid_request_id, farmer_id

Suppose the aid application is to be versioned, then the new version of the application is created by cloning the initial application, so a copy of the row(s) in the original table is created and inserted there.

We can create an ordering of the tables to be processed. We start with the independent tables first.
To clone the row(s) related to the aid request in farmer_project_item_estimate, how would we do so?
We would create the three tables it depends upon, and then use the ids farmer_id, project_item_id, aid_request_id to insert a row in this table, since the foreign keys are not null.
Is there a general way or an algorithm to do this? would I have to create a dependency graph - but what if there is a 1:N relationship (so multiple rows are associated as in project_item). I am wondering how one can do it in SQL, or SQL and Java?

this isn't duplicating a row, this is duplicating a comprehensive set of related rows

you'll have to write some application logic to do that