Search code examples
rrsqlite

Create SQLite table with primary key in R

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)
Solution

  • You could use dbExecute and send directly an SQL command:

    library(DBI)
library(RSQLite)
conn <- dbConnect(SQLite())
dbExecute(conn, "
CREATE TABLE mytable
(
  a INTEGER PRIMARY KEY, 
  b TEXT
)")

# Write one row
dbExecute(conn,"insert into mytable values(1,'test')")
# Try to violate primary key
dbExecute(conn,"insert into mytable values(1,'test')")

[1] 0
[1] 1
Error: UNIQUE constraint failed: mytable.a