0

I'm trying to design a table in SQLite3, where I can perform these 2 actions.

INSERT new row where a unique primary key is created.

And

INSERT new row with an given primary key

Think of a scenario where 2 separate SQLite databases want to share rows created in either table, let's call the table "Messages". Let forget about how the information will get to from one db to another. Let's simply focus on the actual INSERT of the data.

Scenario A:

  1. (t=0) DB-1 INSERTS new row at time 0, creating a new, DB-1 unique primary key. (PK=1)
  2. (t=1) DB-1 reads the inserted line and sends it to the DB-2 for insertion there.
  3. (t=2) DB-2 Gets the new line and INSERTS new line with given PK (PK=1).

This scenario works fine. My goal is to somehow make scenario B work in a simple way.

Scenario B:

  1. (t=0) DB-1 and DB-2 separately INSERTS new rows at time 0, creating a new, DB-1 and DB-2 unique primary key in each. (PK=1 is present in DB-1 and DB-2)
  2. (t=1) DB-1 reads the inserted line and sends it to the DB-2 for insertion there.
  3. (t=2) DB-2 Gets the new line and INSERTS new row with incremented PK, because 1 already is used. (PK=1).



Let's use the initial table creation SQL as below, how would you change it?

CREATE TABLE Messages 
(
    pk_row INTEGER PRIMARY KEY AUTOINCREMENT,
    created_in_db TEXT, // Identifier for which DB it was created in
    field2 TEXT
);
  • As shown in scenario B, It is not always possible to keep the IDs the same. So why have scenario A at all? – CL. Jul 19 '15 at 17:17
  • Because someone has to make a PK for the row,right? But I think I found a solution where the PK only matters locally in each DB, so when a DB-1 sends a row over to DB-2 for insertion, the PK is not sent with it and DB-2 simply creates a new PK for the inserted row. Little bit of workaround, but does what I need it to do. – Bam Jul 19 '15 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.