search

tidyverse / dbplyr

Database (DBI) backend for dplyr
https://dbplyr.tidyverse.org
Other
469 stars 169 forks source link

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

Open tuge98 opened 2 months ago

tuge98 commented 2 months ago

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 = ',')})"))