I'm looking for the best way to synchronise foreign key relations with SQLRemote. So having e.g. this two tables
CREATE TABLE table1( id integer not null default autoincrement, name varchar(40) NOT NULL, primary key (id) ); CREATE TABLE table_items( id integer NOT NULL default autoincrement, name CHAR(40) NOT NULL, table_1_id integer NOT NULL, cl1 bit default 0, cl2 bit default 0, FOREIGN KEY ( table_1_id ) REFERENCES table_1( id ), PRIMARY KEY (id));
Implementing maybe an invoice with invoice items. cl1 and cl2 shall trigger which client shall this dataset by synched with. But now I'm wondering, what's is the best way to synch also corresponding data of table_1.
A first approach would be from my mind to put also cl1 and cl2 fields into table_1 and add them to pubcliation so publication looks little like:
CREATE PUBLICATION sync_cl1 ( TABLE table_1, TABLE table_items WHERE cl1 = 1 ); CREATE PUBLICATION sync_cl2 ( TABLE table_1, TABLE table_items WHERE cl1 = 2 );
Is there any better way doing this maybe with integrated integrity check?