Skip to Content
0
Former Member
Sep 24, 2017 at 09:29 AM

How to sync the columns btw parents and child table (but add column at the end)?

100 Views

We have the case of parent and child tables with the PK-FK relationship as follow:

PARENT TABLE CHILD TABLE

--------------------- ----------------------

PK_COL# <===== PK_COL#

ATT1 ATT1

ATT2 ATT2

TIME #

Parent table have primary key is PK_COL while the child table has compound primary key PK_COL + TIME

There is a requirement of keeping the child table columns in sync with the parent table, ie. any change of the parent table column (data types changes, rename etc) will immediately reflect in the child table column. So I have setup the replication between Child and Parent for all the columns except the TIME# and this works very well.

However, when adding the new column to the parent table, eg. ATT3 then in the child table it always add after ATT2 as below:

PARENT TABLE CHILD TABLE

--------------------- ----------------------

PK_COL# <=== PK_COL#

ATT1 ATT1

ATT2 ATT2

ATT3 ATT3

TIME #

With this behavior when we generate the delta between 2 model versions, we always get the script for BACKUP CHILD TABLE, DROP CHILD TABLE and Then CREATE CHILD TABLE with new ATT3 and then copy back from the backup. Because normally the Child table is very huge amount of data, this script will take long time to run and finish in the database. We therefore prefer the ALTER Table ADD column syntax, and for this, we need to add the new column ATT3 at the end of the child table.

Is it possible to remove the Replication link and add the column at the end of the child table when adding new column on the parent table with VbScript ? and in which event handler level of the parent table ?