CODEWITHSUNDEEP
flyway
moleboy
moleboy

Reputation: 874

Can flyway handle different IDs across databases

Our tables (Oracle database 11g) have a PK generated by sequences. These IDs are not in synch across environments. If I have a change to Table A, I have to create a script that ignores the PK and uses natural keys. So, for example, if in DEV, I update a part with ID 102843 and paret code YU9, I can't issue an update against prod for ID 102843. I have to issue the update for part code YU9. This gets more and more complicated as more tables become involved since tables join based on the ID.

Anyhow, we've been looking for a tool that can generate sql scripts for this and make my life less of a nightmare. Can Flyway? btw, if anyone knows another such product, I'd love to hear about it (I've talked to Redgate etc. (also, no, I have no control over these sequences, and can not keep them in line. This is just the way it is)

Clarification: I am not asking for how my system should work. This is how it works, and thats not going to change any time soon. I am only asking if the flyway product can help.

Upvotes: 0

Views: 209

Answers (3)

moleboy
moleboy

Reputation: 874

After much arguing, we ended up getting rid of sequences in the other environments. Basically, we cloned PROD down to DEV. Then we got rid of sequences in PROD and now just move the records from DEV to PROD using the IDs generated by the sequences in DEV.
Or, in other words, we simply got rid of them problem.

Upvotes: 1

Primi
Primi

Reputation: 127

To come back to the question (mentioned in the title): Can flyway handle different IDs across databases? We have two different databases with different tables, but we want to map some of the tables/columns of the first db to some other tables/columns of the second db. The main problem is, that the ids are not the same. So is there a way to define, how the ids should be mapped, e.g. on the one side we have a composite primary key, but on the other side we have a automatically incremented id?

Upvotes: 0

Axel Fontaine
Axel Fontaine

Reputation: 35179

It sounds like you have reference data replicated across environments. Why use sequences at all? Just assign manual IDs and your problems will go away.

You could issue a final migration that reassigns manual IDs based on the natural keys and then drops the sequence.

Upvotes: 0

Related Questions