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

[Seth Falcon]

On 3/25/10 5:28 PM, Douglas Bates wrote:
> On Thu, Mar 25, 2010 at 7:20 PM, Gabor Grothendieck
> <ggrothendieck using 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.

You might find a read through the SQLite docs on table creation helpful:
http://sqlite.org/lang_createtable.html

In current versions of SQLite, a column declared as primary key is 
allowed to have NULL.  The table will get an additional key column 
accessible as ROWID that is used internally.  You can specify INTEGER 
PRIMARY KEY in which case values must not be null and this will be an 
alias for ROWID.

+ seth

>
>> 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
>>>
>>
>
> _______________________________________________
> 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


-- 
Seth Falcon | @sfalcon | http://userprimary.net/