View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0003712||SymmetricDS||Bug||public||2018-09-08 09:53||2018-09-13 14:39|
|Target Version||Fixed in Version|
|Summary||0003712: Syncing a table with FK to itself and then issuing a delete * from table on src results in FK error on target|
- you are synchronizing a table that contains a foreign key to itself,
- you have rows in the source table
- you issue a delete * from table on the source
- data (deletes) are captured at the source in an order that doesn't consider fk dependencies
- the batch results in an fk error at the target
|Steps To Reproduce||1. create the following table:|
CREATE TABLE "TEST"(
"ID" INTEGER NOT NULL,
"NAME" CHAR(50) NULL,
PRIMARY KEY ("ID")
ALTER TABLE "TEST"
ADD CONSTRAINT "CONSTRAINT_273C" FOREIGN KEY ("PARENT_ID") REFERENCES "TEST" ("ID");
2. create a simple 2 node sync scenario with this table flowing from server to client
3. Insert the following data into the table:
insert into test values (1,'parent',null)
insert into test values (2,'child',1)
insert into test values (3,'grandchild',2)
4. allow the above data rows to sync from server to client
5. issue a delete * from table on the source node
6. note the capture order of the batch
data_id data_event_type table_name pk_data
245 DELETE TEST "1"
246 DELETE TEST "2"
247 DELETE TEST "3"
7. When data_id 245 is attempted to be deleted on the target, an FK error is received as data_id 246 has fk to 245
Error Message:Referential integrity constraint violation: "CONSTRAINT_273C: PUBLIC.TEST FOREIGN KEY(PARENT_ID) REFERENCES PUBLIC.TEST(ID) (1)"; SQL statement: delete from "DEMO-STORE-001"."PUBLIC"."TEST" where "ID" = ? [23503-176]