Error Code Description: Message: 546, State 1, Severity 16 — 'Foreign key constraint violation occurred, dbname = 'db2', table name = 'table1', constraint name = 'constraint1'

Cause:This issue occurs when data inserted into a foreign key does not match any primary key value in the table referenced by the foreign key

Method 1:

Drop the constraint and resume the thread normally.

Login to Target ASE server:

1> use db2
2> go

1> alter table <table1> drop constraint <constraint1>
2> go

Login to Replication server and resume the thread.

1> resume connection to ASE2.db2
2> go

Method 2:

As the error states that there was an issue while inserting the records on target server on a table (table1) which was having references to its primary table (table2).

 To check that we have checked the constraints for that particular table ('table1')

1> sp_helpconstraint table1

2> go

 name                           definition                                                                                             created

 —————————— ——————————————————————————-

 Reftable2 table1 FOREIGN KEY (emp_id) REFERENCES table2(emp_id) Oct  5 2019  1:34PM

 

Here we have to check whether this primary table is having the value which our transaction was trying to insert, since it was a foreign key table.

we need to login to PRIMARY SERVER (ASE1) and TARGET SERVER (ASE2) with the same query to confirm the records.

     isql -SASE1 -Uuser- Ppassword

use db1
go
select * from table2 where emp_id=10
go

you should see some record on this source database. run the same on target database as well and see if any record exists.

     isql -SASE2 -Uuser- Ppassword

use db2
go
select * from table2 where emp_id=10
go

you may not see any record for this condition. so now we know that record is missing. in such case, we will have to take bcp out of one row from the source and apply it to the target database as shown below and then will resume the thread.

create view view1 as select * from table2 where empl_id=10
go

Next, we will be performing bcp out for that specific record and bcp in into target database.

bcp db1..view1 out view1.bcpout –SASE1 –Uuser –Ppassword –c

bcp db2..table2 in view1.bcpout –SASE2 –Uuser –Ppassword –c

Now, login to replication server and resume the connection normally.

1> resume connection to ASE2.db2
2> go