How does one specify a primary key when using dplyr copy_to()?

Question

I would like to use copy_to() from dplyr to create a new table. Indexes can be easily specified, but I do not see a syntax for specifying a primary key. Is the recommended approach to simply create the table first and then copy data into it using copy_to() as a second step, or is there a single-step solution way to specify primary key in copy_to()?

Answer 1

As mentioned by John in the comment, Hadley gave an example of setting index in an old presentation, archived here. His example:

hflights_db <- src_sqlite("hflights.sqlite3",
create = TRUE)

copy_to(
    dest = hflights_db,
    df = as.data.frame(flights),
    name = "flights",
    indexes = list(
        c("date", "hour"),
        "plane",
        "dest",
        "arr"
    ), temporary = FALSE
)

However, indexes are not primary keys. I digged around in the dplyr and dbplyr source code to see if it is possible to specify a primary key. It seems not. I found a half-dplyr solution. Here's a working example.

#packages
library(pacman)
p_load(nycflights13, dplyr)

#connect to a standard mysql database
#one cannot use sqlite because it doesn't allow one to modify primary keys after creation of a table, but one could create the key at creation
#but that would mean one cannot use copy_to
hflights_db = src_mysql("test", host = "127.0.0.1", username = "root", password = "root")

#does work?
hflights_db

#add a unique value to use as primary key
flights$key = 1:nrow(flights)

#copy flights data
copy_to(
  dest = hflights_db,
  df = as.data.frame(flights),
  name = "flights",
  indexes = list(
    "key",
    c("year", "month", "day", "hour", "minute"),
    "tailnum",
    "dest",
    "origin",
    "carrier"
  ),
  temporary = FALSE,
)

#set primary key
dbExecute(hflights_db$con, 
          "ALTER TABLE flights
           ADD PRIMARY KEY (`key`);"
          )


#check status
tbl(hflights_db, "flights")

output:

# Source:   table<flights> [?? x 20]
# Database: mysql 5.7.19-0ubuntu0.16.04.1 [[email protected]:/test]
    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier flight tailnum origin  dest air_time distance  hour
   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>     <dbl>   <chr>  <int>   <chr>  <chr> <chr>    <dbl>    <dbl> <dbl>
 1  2013     1     1      517            515         2      830            819        11      UA   1545  N14228    EWR   IAH      227     1400     5
 2  2013     1     1      533            529         4      850            830        20      UA   1714  N24211    LGA   IAH      227     1416     5
 3  2013     1     1      542            540         2      923            850        33      AA   1141  N619AA    JFK   MIA      160     1089     5
 4  2013     1     1      544            545        -1     1004           1022       -18      B6    725  N804JB    JFK   BQN      183     1576     5
 5  2013     1     1      554            600        -6      812            837       -25      DL    461  N668DN    LGA   ATL      116      762     6
 6  2013     1     1      554            558        -4      740            728        12      UA   1696  N39463    EWR   ORD      150      719     5
 7  2013     1     1      555            600        -5      913            854        19      B6    507  N516JB    EWR   FLL      158     1065     6
 8  2013     1     1      557            600        -3      709            723       -14      EV   5708  N829AS    LGA   IAD       53      229     6
 9  2013     1     1      557            600        -3      838            846        -8      B6     79  N593JB    JFK   MCO      140      944     6
10  2013     1     1      558            600        -2      753            745         8      AA    301  N3ALAA    LGA   ORD      138      733     6
# ... with more rows, and 3 more variables: minute <dbl>, time_hour <chr>, key <int>

dplyr doesn't show the indexes or keys, as far as I can tell. So I used DBeaver to confirm: