FIX: Distribution Agent Fails when Applying Snapshot if the Table has a Self-referencing Foreign Key (303218)

SYMPTOMS

If a transactional publication has a table with a self-referencing foreign key, and the table article is configured to include declared referential integrity in a snapshot, the Distribution Agent fails while trying to reapply the snapshot after the first synchronization. The Distribution Agent fails with this error message:

Unable to drop table [Table Name], because it is accessed by a Foreign Key constraint.

CAUSE

When applying the snapshot, the schema files are applied in such a way that the child tables are dropped, and then re-created before the parent tables are re-created. So, even if there are foreign key references, tables can be dropped and re-created successfully. However, if there is a self-referencing foreign key, you must remove the foreign key before you can drop the table. When the Distribution Agent tries to apply the schema of a table that has a self-referencing foreign key the second time, the agent fails with the error message shown in the "Symptoms" section.

This problem occurs if the pre_creation_command option for the snapshot is Drop the existing table and recreate it, and the table article is configured to include declared referential integrity in the snapshot.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

MORE INFORMATION

Steps to Reproduce Behavior

Create two tables. In one table create a self-referencing key.
For example:

Create table Bugtest
(
Col1 Int Primary Key ,
Col2 int Foreign Key references Bugtest(Col1)
)


Create table Bugtest1
(
Col1 Int Primary Key Foreign Key references Bugtest(Col1),
Col2 int <BR/>
)

Create a transactional publication with the two tables you created in step 1 as articles.
Navigate to the Table Article Properties/Snapshot, and enable Include declared referential integrity for each article.
Create a subscription, and then apply the snapshot. This works fine.
Reinitialize the subscription, re-run the Snapshot Agent, and then run the Distribution Agent to apply the snapshot.

The Distribution Agent fails with this error message:

"Could not drop object 'Bugtest' because it is referenced by a FOREIGN KEY constraint."

If you remove the self-referencing foreign key, the Distribution Agent works fine, even though there is another foreign key that references the table BugTest.