[R-sig-DB] Extend dbWriteTable to specify a primary key

[Gabor Grothendieck]

You don't have to define column types when you create a table in
SQLite so this works:

s <- sprintf("create table %s(%s, primary key(%s))", "DF",
		paste(names(DF), collapse = ", "),
		names(DF)[1])
dbGetQuery(con, s)
dbWriteTable(con, "DF", DF, append = TRUE, row.names = FALSE)

On Thu, Mar 25, 2010 at 7:05 PM, Douglas Bates <bates using stat.wisc.edu> wrote:
> I am going to create a view from a select over several tables that are
> created in R.  I purposely generate a value suitable as a primary key
> in these tables but I have not decided how to specify it in a
> dbWriteTable.  Instead I go through a complicated operation of
> creating the table with a few records, dumping the create table
> definition that was used, editing it to declare the primary key,
> dropping the table, creating the table from SQL with the primary key
> and then using dbWriteTable with append = TRUE.
>
> Is there a better way?  This seems somewhat baroque. If no better way
> currently exists would it be reasonable to specify a primary key in a
> dbWriteTable call.
>
> I should say that I am using RSQLite and SQLite apparently doesn't
> allow you to declare a primary key in an ALTER TABLE statement.
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>