Re: Migrating from exposed, compund PKs to single-column db generated keys

Hi Hugi,

Are the legacy systems that are accessing this DB doing inserts?  If so,
check if the DB has a way to do an automatic generated PK for that column's
value if it is null upon insert.  If the inserts are only done from the
Cayenne-based system, you'll be OK there.  Not sure if there'd be
update/delete implications from the legacy system, but I suspect these may
be OK.


On Thu, Jul 9, 2020 at 9:17 AM Hugi Thordarson <[email protected]> wrote:

> Hi all,
>
> I'm currently in the progress of migrating an old DB schema from using
> exposed meaningful PKs to using db generated (serial) surrogate PKs.
> However, some legacy systems still use the same DB, so my new PKs will
> have to coexist alongside the older ones for a while.
>
> My current plan:
> - Each table gains a new column named "id" of the type serial, with a
> unique constraint applied to it.
> - The corresponding Cayenne DbEntity is changed to use the new "id" column
> as the PK with a PK-generation strategy of "Database generated".
> - Every table with relationships to this entity gets appropriate FKs that
> reference the new "id" column instead of the old columns (and their
> corresponding DbRelationships get changed as well)
>
> Note that the tables will keep their old PK as the real, defined PK — the
> "id" column is a plain addition.
>
> So, my question is: Does anyone see a problem with this? What this
> essentially means is that for a while, Cayenne will be using a "fake"
> primary key to identify objects, i.e. not the table's actual defined PK but
> just a regular unique integer column.
>
> Initial testing seems to suggest that this works well, but I just wanted
> to check if I could expect something horrid to happen down the line :).
>
> Cheers,
> - hugi