1

I would like to add an autoincrementing integer field called uid to an existing table assoc, but it doesn't look like I can do that unless it's a primary key.

I have fields local_id and remote_id which are the existing primary key pair, and I do that so that I can INSERT OR IGNORE INTO assoc so that I don't get duplicate primary keys, but if I have a pair of columns as a primary key, I can't seem to use them as an update (see other SO question).

Could anyone suggest how to restructure the table (and implement that restructuring using ALTER TABLE) so that I can get the behavior I need:

  • a single autoincrementing key, so I can use that for UPDATEs
  • a pair of fields local_id and remote_id so that the pair (local_id, remote_id) remains unique in the table
Community
  • 1
  • 1
Jason S
  • 184,598
  • 164
  • 608
  • 970

2 Answers2

1

In this case, you could drop the primary key on your existing columns, create the new primary key integer autoincrementing column, then create a UNIQUE index on the other two columns.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • cool, that's a good alternative. Hey, if there's a builtin rowid, why would I ever want the autoincrementing primary key? – Jason S Jan 07 '11 at 04:47
  • 1
    If I understand the docs correctly, if you declare INTEGER PRIMARY KEY it simply aliases the ROWID value. If you explicitly declare INTEGER PRIMARY KEY AUTOINCREMENT you get slightly different (safer) behavior. Here's the man page: http://www.sqlite.org/autoinc.html. – Larry Lustig Jan 07 '11 at 04:49
0

Aha, I don't need to -- there's a builtin rowid column.

Jason S
  • 184,598
  • 164
  • 608
  • 970
  • It's not a good idea to rely on rowid to link to other tables as the rowid can get reassigned to a new item if the first item is deleted – Kevin Feb 21 '14 at 16:18