Skip to Content
avatar image
Former Member

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

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 ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 27, 2017 at 04:27 PM

    Hello Socola

    The answer is simple - just move the new column to the required position in the list of columns, in:

    * the Browser

    * the table symbol

    * the list of columns in the Table properties

    Well, it works for me :)

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 28, 2017 at 08:08 AM

    Hi George,

    Life is not so simple :).

    The whole child table has been synchronised with the parent table based on Table Replication. Thus all its columns from (#PK_COL, ATT1, ATT2) except its own partial compound key #TIME have been in syn with the Parent columns. Therefore you can not move or change anything on those columns including the moving. It is simply read only and let you no chance to change.

    I would like to have whenever I have added the new column ATT3 in Parent table

    Parent_Tab (#PK_COL, ATT1, ATT2,ATT3) then the child table should be like that Child_Tab ((#PK_COL, ATT1, ATT2,#TIME, ATT3) and those activities must be automatically done when the user add new column on the Parent table, thus I think only way could be done is writting VB Script with event handling on the Parent Validate or something like that.

    Add comment
    10|10000 characters needed characters exceeded

    • I wasn't able to duplicate this scenario -

      • If column replication is turned on, the replica table has exactly the same columns as the original, so how did you manage to add #TIME to the replica table?
      • If column replication is turned off, I can add my own columns to the replica, but new columns in the original are not mirrored in the replica.

      Please supply a screen shot of replication options for the table and Key