rows_delete fails due to key constraints: Can’t Modify Database Table

[tuge98]

When attempting to delete observations from the source database using the rows_delete function, we encounter an issue related to key constraints. Specifically, the process fails due to these constraints. However, running a native SQL query for the same task works as expected.

rows_delete(dm_tbl, tbl, copy = copy, in_place = TRUE, by = by, unmatched = "ignore", returning = !!key)

gives the error:  
Error in rows_delete(dm_tbl, tbl, copy = copy, in_place = TRUE, unmatched = "ignore",  : 
Can't modify database table "batch".
Caused by error:
! nanodbc/nanodbc.cpp:2809: 00000: The DELETE statement conflicted with the REFERENCE constraint "FK__data_upload__26F97175".

However native SQL clause will work just fine:

dbExecute(dm_get_con(dm), glue("DELETE FROM batch WHERE batch_id IN ({paste0(batch_id, collapse = ',')})"))