Extend dbWriteTable to specify a primary key

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.

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 at 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.

On Thu, Mar 25, 2010 at 7:20 PM, Gabor Grothendieck
<ggrothendieck at gmail.com>  wrote:

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)

Thanks.

On Thu, Mar 25, 2010 at 7:05 PM, Douglas Bates<bates at 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.