Create SQLite table with primary key in R

[rjss]

I am trying to create a table with a primary key for an SQLite database. Is there a way to do this with DBI? I have looked in the documentation but cannot see any examples. I am trying something like the example below.

library(DBI)
library(RSQLite)

conn <- dbConnect(RSQLite::SQLite(), "")
DBI::dbCreateTable(conn, "mytable", c(a = "integer", b = "text"), 'PRIMARY KEY("a")')
dbDisconnect(conn)

[DavoWW]

Hi @rjss,
After a bit of googling I found (and extended) this example that works:

# After: https://stat.ethz.ch/pipermail/r-sig-db/2010q1/000813.html
library(DBI)
library(RSQLite)

# Get some suitable data, add a primary key column, and
# change the column names to meet requirements
DF <- iris
DF$keycol <- row.names(DF)
colnames(DF) <- c("SpL","SpW","PtL","PtW","Species","keycol")
head(DF)
#>   SpL SpW PtL PtW Species keycol
#> 1 5.1 3.5 1.4 0.2  setosa      1
#> 2 4.9 3.0 1.4 0.2  setosa      2
#> 3 4.7 3.2 1.3 0.2  setosa      3
#> 4 4.6 3.1 1.5 0.2  setosa      4
#> 5 5.0 3.6 1.4 0.2  setosa      5
#> 6 5.4 3.9 1.7 0.4  setosa      6

# Generate the SQL string to create the db
s <- sprintf("create table %s(%s, primary key(%s))", "DF",
                 paste(names(DF), collapse = ", "),
                 names(DF)[6])
s
#> [1] "create table DF(SpL, SpW, PtL, PtW, Species, keycol, primary key(keycol))"

# Make a transient db in memory
con <- dbConnect(RSQLite::SQLite(), ":memory:")
con
#> <SQLiteConnection>
#>   Path: :memory:
#>   Extensions: TRUE

# Create table and then write data to it
dbExecute(conn=con, statement=s)
#> [1] 0
dbWriteTable(con, "DF", DF, append = TRUE, row.names = FALSE)

# Read from the db and check it
test <- dbGetQuery(con, 'SELECT * FROM DF WHERE "SpL" < 4.6')
test
#>   SpL SpW PtL PtW Species keycol
#> 1 4.4 2.9 1.4 0.2  setosa      9
#> 2 4.3 3.0 1.1 0.1  setosa     14
#> 3 4.4 3.0 1.3 0.2  setosa     39
#> 4 4.5 2.3 1.3 0.3  setosa     42
#> 5 4.4 3.2 1.3 0.2  setosa     43

# Shows that the "keycol" is the pk (primary key)
dbGetQuery(con, 'PRAGMA table_info(DF)')
#>   cid    name type notnull dflt_value pk
#> 1   0     SpL            0         NA  0
#> 2   1     SpW            0         NA  0
#> 3   2     PtL            0         NA  0
#> 4   3     PtW            0         NA  0
#> 5   4 Species            0         NA  0
#> 6   5  keycol            0         NA  1

^{Created on 2021-01-21 by the reprex package (v0.3.0)}

Thanks for the question - I learnt something useful today!