CODEWITHSUNDEEP
sql-serveradoforeign-key-relationship
Itaypk
Itaypk

Reputation: 1133

SQL Server merge replication causes foreign key failures

In my application, using SQL Server 2005, I'm having two tables, let's call them Table A and Table B; a foreign key constraint is defined on Table B, referencing the primary-key column in Table A, which is an auto-generated integer ID. I'm running the following simple transaction:

Start transaction
Insert a row to table A
Retrieve the last-generated ID ("SELECT @@IDENTITY ... ")
Insert data to table B, using this ID
Commit

It all works well, until I'm trying to create merge replication (continuous) with another SQL Server 2005. Both publisher and subscriber now fail this transaction when trying to insert data to table B, because of foreign key constraint failure:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TableB_TableA". The conflict occurred in database "MyDB", table "TableA", column 'ID'.

I was not able to make it work by committing after inserting data to table A. However, after removing the merge replication, everything worked. The database code is written in C++, using ADO.

Is the replication interfering with the transaction anyhow? Any other possible explanation?

Upvotes: 1

Views: 1111

Answers (2)

Itaypk
Itaypk

Reputation: 1133

Looks like the issue is related to the scope of the @@IDENTITY function. When I used LAST_IDENT('TableB') instead, things seem to work.

As described in MSDN:

  • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Upvotes: 0

John Sansom
John Sansom

Reputation: 41879

Are the Primary Key values on Table A at both server nodes discrete from one another (In other words are you using Identity Range management at each node)?

Also, has the Foreign Key constraint been configured with the Not For Replication property?

I would assume that because your Foreign Key constraint has already been enforced locally at the Publisher, that you do not need to re-check it when merging with the Subscriber.

Upvotes: 1

Related Questions