1 Reply Latest reply on Feb 16, 2009 1:50 AM by wijszman

    composite key with auto increment

    wijszman
      I'm trying to create a table with two primary keys (a composite key).

      I can achieve that with this code:

      CREATE TABLE IF NOT EXISTS testtable (
      id INTEGER ,
      user_id INTEGER ,
      updated date NOT NULL DEFAULT current_timestamp ,
      PRIMARY KEY(id,user_id)
      )

      However, i would like one of them to auto increment.

      CREATE TABLE IF NOT EXISTS testtable (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER PRIMARY KEY,
      updated date NOT NULL DEFAULT current_timestamp
      )

      This query returns a 3115 error (useless error imo, 'no such table' seems irrelevant in this query).

      The auto increment only seems to work if you define the primary key in the same line, but this prevents composite keys.

      Is there any way to achieve this or is it impossible?