why is r/sqlite requiring a primary key?

Question

so i have a table that was created using:

dbGetQuery(conn, "CREATE TABLE Data (DataID integer primary key autoincrement, 
           DataTypeID integer, DataName varchar)")

after some code, i create a data frame called "insertdata" that is like this:

    type    name
1   1   Data1
2   1   Data2
3   1   Data3
4   1   Data4
5   1   Data5

and i use the following to insert it into my table:

dbWriteTable(conn, "Data", insertdata, append=TRUE)

later on i create another dataframe to insert of a different type, like this:

    type    name
1   2   OtherData1
2   2   OtherData2
3   2   OtherData3
4   2   OtherData4
5   2   OtherData5

but when i use the same dbWriteTable as before, it tells me

"Warning message:
In value[[3L]](cond) :
  RS-DBI driver: (RS_SQLite_exec: could not execute: PRIMARY KEY must be unique)"

Why is that? My table is set to auto-increment the primary key. I don't understand where the non-unique characters would be coming from. Is it because the rownames are the same in each?

Answer 1

Yes the error occurs because the rownames are the same for each row. You have created a table with an autincrement ID or ROWID. When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine.

When you use

 dbWriteTable(conn, "Data", insertdata, append=TRUE)

Here you use the option row.names = TRUE, so the rownames are used as the increment ID. Since your 2 data.frame have the same rownames , you get the error. The usual work-around is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert statement.

For example you can do this :

driver <- dbDriver("SQLite")
conn <- dbConnect(driver, dbname='DB_KEY')
## I read all the table to get rows number, you can optimize this with a clever select
index <- nrow(dbReadTable(conn,'Data'))
rownames(mydf) <- 1:nrow(mydf) +index
dbWriteTable(conn, "Data", mydf, append=TRUE)
dbDisconnect(conn)