Merge replication with foreign key constraints

Let's say I have a database with three tables. One for customers, and two
for the invoices. Customers have cust_id field, wich is 'surogate' key, it
contains the org_id where the customer was created. That table should be in
merge replication because I need to be able to add data and make changes on
all locations in the replication system.
The invoices are referenced between them self with foreign key (you can't
insert invoice_id in the detail table if the invoice_id doesn't exsist in
master table), and the invoices table is referenced with customer table with
foreign key, so you can't add cust_id to invoices wich doesn't exsist in
customers table).
The DDL is like this:
CREATE TABLE [dbo].[customers] (
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
[cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoice_details] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[item_id] [int] NOT NULL ,
[item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
[item_price] [decimal](18, 11) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[invoices] (
[invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
[invoice_date] [datetime] NOT NULL ,
[remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[cust_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
(
[invoice_id],
[item_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
(
[invoice_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[customers] ADD
CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
[rowguid]
GO
CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[invoice_details] ADD
CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[invoices] (
[invoice_id]
) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[invoices] ADD
CONSTRAINT [FK_invoices_customers] FOREIGN KEY
(
[cust_id]
) REFERENCES [dbo].[customers] (
[cust_id]
) NOT FOR REPLICATION
GO
For the purpose I'm creating two publications. One for the customers, with
no filtering, because I need to have all the subscribers share the same
data. The other one is for the invoices, but those are filtered within
invoice_id, because I don't want one subscriber to have data that doesn't
belong to it.
So, I create the first merge publication, and at the end SQL server gives me
this:
This publication contains references to foreign keys outside the
publication. The following tables are outside the publication, but contain
foreign keys that are referenced from inside the publication:
invoices (references 'customers')
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Why do I need to add invoices table to the publication?
The other one, when creating publication for the invoices gives me more
headache:
This publication contains references to primary keys outside the
publication.
The following tables are outside the publication, but contain primary keys
that are referenced from inside the publication:
-- customers (referencing table is 'invoices')
Although you can change existing data in the referencing tables, you will
not be able to add rows to those tables. If you want to add rows to the
referencing tables, include the referenced tables as articles in the
publication.
To add tables to the publication, select the publication in the Create and
Manager Publications dialog box, and then click Properties & Subscriptions.
Again, why do I need to have customers table in the same publication with
invocies' tables? Since I have several publications for the invoices, each
filtering for one particular subscriber, if I add customers to the
publication, I need to add it for every publication I create. This seems
like a waste of resources. Isn't it easier to have just one publication for
the customers?
This is, of course, just a small example derived from the real world
situation. I have several 'primary key' tables (customers, articles, users,
delivery_rates, tax_rates, organizational departments, stocks,
blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
(invocies, stock documents, bills, ...). And, somehow, putting ALL those
tables within the same publication seems a bit messy. I prefer having
similair groups of tables together (for instance, publication for stock
documents has 22 articles, but, the 'primary key tables' that those tables
reference to are in it's own seperate publications).
Am I doing something wrong with my design?
Any help much appreciated!
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
The reason you would need to include all articles related by fk pk
constraints is that you might add an row to a child table which you are
replicating on the publisher where the row exists on the parent table. Then
this child row travels to the subscriber where the parent row does not
exist, and when the constraint is enforced, the transaction is rolled back
on the subscriber and publisher.
Similarly you might delete a parent record on the subscriber, and then when
it hits the publisher it might want to delete all child records belonging to
that parent row if you are not enforcing the constraint for replication, and
if you have cascading deletes and updates.
Under some circumstances you can ignore this warning.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncqtm2o.pol.majk@.fly.srk.fer.hr...
> Let's say I have a database with three tables. One for customers, and two
> for the invoices. Customers have cust_id field, wich is 'surogate' key, it
> contains the org_id where the customer was created. That table should be
> in
> merge replication because I need to be able to add data and make changes
> on
> all locations in the replication system.
> The invoices are referenced between them self with foreign key (you can't
> insert invoice_id in the detail table if the invoice_id doesn't exsist in
> master table), and the invoices table is referenced with customer table
> with
> foreign key, so you can't add cust_id to invoices wich doesn't exsist in
> customers table).
> The DDL is like this:
> CREATE TABLE [dbo].[customers] (
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_name] [char] (50) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_address] [char] (150) COLLATE Croatian_CI_AS NOT NULL ,
> [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoice_details] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [item_id] [int] NOT NULL ,
> [item_description] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL ,
> [item_price] [decimal](18, 11) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[invoices] (
> [invoice_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [cust_id] [char] (5) COLLATE Croatian_CI_AS NOT NULL ,
> [invoice_date] [datetime] NOT NULL ,
> [remark] [varchar] (250) COLLATE Croatian_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] WITH NOCHECK ADD
> CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
> (
> [cust_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] WITH NOCHECK ADD
> CONSTRAINT [PK_invoice_details] PRIMARY KEY CLUSTERED
> (
> [invoice_id],
> [item_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoices] WITH NOCHECK ADD
> CONSTRAINT [PK_invoices] PRIMARY KEY CLUSTERED
> (
> [invoice_id]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[customers] ADD
> CONSTRAINT [DF__customers__rowgu__49C3F6B7] DEFAULT (newid()) FOR
> [rowguid]
> GO
> CREATE UNIQUE INDEX [index_357576312] ON [dbo].[customers]([rowguid]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[invoice_details] ADD
> CONSTRAINT [FK_invoice_details_invoices] FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[invoices] (
> [invoice_id]
> ) NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[invoices] ADD
> CONSTRAINT [FK_invoices_customers] FOREIGN KEY
> (
> [cust_id]
> ) REFERENCES [dbo].[customers] (
> [cust_id]
> ) NOT FOR REPLICATION
> GO
>
> For the purpose I'm creating two publications. One for the customers, with
> no filtering, because I need to have all the subscribers share the same
> data. The other one is for the invoices, but those are filtered within
> invoice_id, because I don't want one subscriber to have data that doesn't
> belong to it.
>
> So, I create the first merge publication, and at the end SQL server gives
> me
> this:
> This publication contains references to foreign keys outside the
> publication. The following tables are outside the publication, but
> contain
> foreign keys that are referenced from inside the publication:
> invoices (references 'customers')
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Why do I need to add invoices table to the publication?
> The other one, when creating publication for the invoices gives me more
> headache:
> This publication contains references to primary keys outside the
> publication.
> The following tables are outside the publication, but contain primary
> keys
> that are referenced from inside the publication:
> -- customers (referencing table is 'invoices')
> Although you can change existing data in the referencing tables, you
> will
> not be able to add rows to those tables. If you want to add rows to the
> referencing tables, include the referenced tables as articles in the
> publication.
> To add tables to the publication, select the publication in the Create
> and
> Manager Publications dialog box, and then click Properties &
> Subscriptions.
> Again, why do I need to have customers table in the same publication with
> invocies' tables? Since I have several publications for the invoices, each
> filtering for one particular subscriber, if I add customers to the
> publication, I need to add it for every publication I create. This seems
> like a waste of resources. Isn't it easier to have just one publication
> for
> the customers?
> This is, of course, just a small example derived from the real world
> situation. I have several 'primary key' tables (customers, articles,
> users,
> delivery_rates, tax_rates, organizational departments, stocks,
> blaha-blaha-blaha), and several dozens of 'foreign key referencing' tables
> (invocies, stock documents, bills, ...). And, somehow, putting ALL those
> tables within the same publication seems a bit messy. I prefer having
> similair groups of tables together (for instance, publication for stock
> documents has 22 articles, but, the 'primary key tables' that those tables
> reference to are in it's own seperate publications).
> Am I doing something wrong with my design?
> Any help much appreciated!
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
|||On 2004-12-02, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> The reason you would need to include all articles related by fk pk
> constraints is that you might add an row to a child table which you are
> replicating on the publisher where the row exists on the parent table. Then
> this child row travels to the subscriber where the parent row does not
> exist, and when the constraint is enforced, the transaction is rolled back
> on the subscriber and publisher.
Yes, I'm aware of that. But, both tables do exsits on both publisher and
subscriber. The application run at subscriber can't violate constraint, so
can't application on the publisher. Constraints are created with NOT FOR
REPLICATION, so if replication job first inserts child table - it will work.

> Similarly you might delete a parent record on the subscriber, and then when
> it hits the publisher it might want to delete all child records belonging to
> that parent row if you are not enforcing the constraint for replication, and
> if you have cascading deletes and updates.
Yes, the same thing.

> Under some circumstances you can ignore this warning.
Thank you for your response. I recreated publisher/subscriber situation in
'lab', and it seems to be working fine (i just ignored the warning). I'm
able to insert data into foreign key table (as long as I have primary key
table with up-to-date data), replication works fine, everything is ok.
But, now I have another issue. Since the number of subscribers is going to
be rather high, some 200-300 subscribers (all MSDEs on laptops) i wanted to
script the subscriptions. In snapshot options for the particular publication
i choose the snapshot agent to DROP and recreate tables on the subscriber,
referential integrity included. But, publication wizzard tells me it can't
create foreign key constraints because the reffered tables are not in that
publication. Wich I understand, because, if I first push the invoices
publication to the subscriber, there is customers table missing, so, realy
no constraints to that tabe could be created.
Is there workaround for this? I need to have initial snapshots to delete all
the data on the remote side. But, putting all the tables to one publications
seems like a LOT of mess here. In my example, if I have 50 subscriptions I
should publish the customers table 50 times, instead of just once.
So, my question is again, am I doing something wrong when designing the
replication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr