Re: [Modeling-users] _sqlite.IntegrityError:PRIMARY KEY must be unique

[Sebastien B. <sbi...@us...>]

        Hi,

Mario Ruggier <ma...@ru...> wrote:
> I am getting this error (subject line) when I try to insert a new
> object into a table. The PK in question is not a class property, and
> I do not mess with it in any way. The situation is that I had a bunch
> of rows already in this table, and dumped the sql for them to be
> able to recreate them. I then recreated the sqlite db to reflect changes
> to the model, and i re-inserted the previously existing rows for the one
> table (which is completely untouched by the model changes). Doing
> a "select * from table" gives the rows correctly.
>=20
> However, since then, the primary key count for newly created objects
> has restarted at 1, and each time i attempt to insert a new object,
> he (the EC) increments this, but since there are already so many in the
> db, the insertion fails with the above error.
>=20
> What's going on?

The pk generation uses either a sequence or a specific table (sqlite
uses a specific table, because it lacks support for sequences).

  When restoring your db, you need to restore these tables and their
  values as well.

Say you have a table book, then the corresponding table for pk
generation is: PK_SEQ_BOOK

  You can update it by:

  1. select max(id) from BOOK;

  2. delete from PK_SEQ_BOOK;

  3. insert into PK_SEQ_BOOK value(<n>);  --where <n> is the result of
     step 1.

Note that if you use inheritance, step 1. should be performed against
all tables in the inheritance tree, and you'll use the maximum of these
numbers in step 3. Last, the name of the table/sequence if always
PK_SEQ_<name>, where name is in general the external name of the root
entity (see Entity.primaryKeyRootName() and SQLiteAdaptorChannel's
primaryKeysForNewRowsWithEntity()).

Last note: when using a table for PK generation, it should have only one
row, this is why step 2. is there.

-- S=E9bastien.