I have a database structure with several default empty tables defines in an admin schema. For example :
CREATE TABLE admin."ConfigurableWindowGUIEntryInternal" ( "DbSyncID" bigint NOT NULL, "DbSyncInsertedBackupVersion" bigint NOT NULL, ... ) WITH ( OIDS=FALSE );
And, for each client, I have a dedicated schema with tables that derive from those admin tables, and use the two first elements as primary keys (DbSyncID and DbSyncInsertedBackupVersion). For example :
This schema is used for synchronisation of the database between the server and client applications and I do not want to change anything in this communication protocol.
But now, I would want to develop a server-based application. And this application would need a single column unique ID for referencing tables entries. My current primary key does not fit my needs as it is based on two columns. So, I would want to add a new field on all derived tables. And, as I don't want to change the synchronisation stuff, the values of this new field should be assigned automatically.
Is there a way to add this once for all by changing the parent table or do I need to add the field for each derived table ? And how can I perform this, taking into account that the tables already contain data ?
