title: How to append to SQLite table in R with autogenerated fields
body: <p>This is a similar problem to <a href="https://stackoverflow.com/q/19877661/1623354">this question</a>, but I do not want the missing columns filled in with NA, because the missing columns have meaningful default values including the primary key. </p>
  
  <p>I am trying to append to a SQLite table from R where the table has some auto-generated fields, specifically the primary key, and two timestamp values. The first timestamp is the created date, and the second timestamp is a modified date. </p>
  
  <p>Here is the table structure:</p>
  
  <pre><code>CREATE TABLE "level1" (
    "l1id" bigint(20)  NOT NULL ,
    "l0id" bigint(20)  DEFAULT NULL,
    "acid" bigint(20)  DEFAULT NULL,
    "cndx" int(11) DEFAULT NULL,
    "repi" int(11) DEFAULT NULL,
    "created_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "modified_date" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    "modified_by" varchar(100) DEFAULT NULL,
    PRIMARY KEY ("l1id")
  )
  </code></pre>
  
  <p>When I have tried doing the exact same thing using MySQL, <code>dbWriteTable</code> automatically handles the default values for missing columns, and populates the primary key and created_date properly (AND it matches the order of the columns automatically).</p>
  
  <p>How can I achieve the same behavior with the RSQLite package? I am not sure if I have the database configured incorrectly, or if I need some addtional steps within R?</p>
  
  <p>I have tried pre-populating the missing fields with <code>NA</code> &amp; <code>'null'</code>, but in both cases I get an error saying:</p>
  
  <pre><code>Warning message:
  In value[[3L]](cond) :
    RS-DBI driver: (RS_SQLite_exec: could not execute: column l1id is not unique)
  </code></pre>
  
  <p>And the data does not get written. </p>