Hello everyone,
As per the title, I am trying to create an .sqlite database in RStudio, and import and update data tables using .csv files (that are in a tidy data format).
While I've successfully been able to create the initial database file and import .csvs, I am struggling with using R to specify specific column attributes such as primary keys and foreign key constraints, as there doesn't seem to be any arguments for them using DBI::dbWriteTable
.
I know how to use the UPDATE
query via RSQLite to modify existing records in a table (based off of values in another table), but I only know how to do so on a per-column basis, or needing to delete the rows I want to update, and re-insert them with the new values. For data frames, I would simply using dbplyr::coalesce
to merge the two tables together, replacing null values, but I don't know how to do the same thing to an SQLite table.
Does anyone have experience in managing an SQLite database this way, or am I better off using a GUI such as DB Browser for creating and modifying tables and column attributes, and sticking to R for queries and analyses?