SQLite importing data from a datadump script including primary and foreign keys

I have a SQLite database that makes use of foreign keys, some of which will be autoincremented values. The "core" data the system represents is for example a car. The foreign keys are linking to information about wheels and tyres for example, and I wish to export n cars from one database and import into another.

I want to do this by writing a set of sql statements (i.e a bunch of insert statements) that can be loaded by the importing database, but the key values in the dumped data will not necessarily match up with the existing data (maybe there are duplicates in some of the key values).

What is the best way to deal with this? Is there an easy or recommended way to write the import script so that dependencies on exported key values are removed?

In the example below, a carindex will name a car. CarPartColours links a single part and with a colour defin开发者_运维百科ition. There will be multiple rows in CarPartColours with the same CarID. I wish to export all the relevant rows from carpartcolours, carindex, parts and colours when the user selects a single row in carindex, and import into another database. The colour definitions in that database may be duplicates (another different issue) or have the same key values as those in the origin db.

 CREATE TABLE carindex (
  ID    integer PRIMARY KEY NOT NULL,
  Name  varchar(50)
);

CREATE TABLE carpartcolours (
  ID        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  CarID     integer,
  PartID    integer,
  ColourID  integer,
  /* Foreign keys */
  FOREIGN KEY (CarID)
    REFERENCES carindex(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (PartID)
    REFERENCES parts(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
  FOREIGN KEY (ColourID)
    REFERENCES colours(ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

CREATE TABLE colours (
  ID    integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  Name  varchar(50),
  R     real,
  G     real,
  B     real
);

CREATE TABLE parts (
  ID            integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  Name          varchar(50),
  Value         real,
  Manufacturer  varchar(50)
);

---

@Mike I posted a previous answer, and I was on a totally wrong train of thought before, so I'm starting fresh. My apologies.

I would say that you need to make sure you look into database master slave replication, as that's what you're trying to do. You want to replicate the data from the slave to the master. Since you're not going to know which was inserted where when, then you need to look for a collision free key (or try for something collision free). So because you may generate a record in any one database and you may migrate that record to any other database, then you want to generate a UUID style key, and use that in place of a INT AUTOINCREMENT.

This is the only way to do cross database data replication.

Otherwise, you just want to insert into carpartcolours last.

Sorry for the delay in answering your question ...

---

Try wrapping all your insert statements into transaction:

BEGIN TRANSACTION
// all your inserts go here
END TRANSACTION

---

I'm not familiar with sqlite per se, but what I have done in the past with similar problems is:

• dump out data from origin database, including primary and foreign key values
• switch off auto-increment behaviour in target database
• import data
• switch auto-increment behaviour back on

This may not be possible in sqlite, or may not work because you have pre-existing data with the same primary keys.

In this case, you can import the data from the origin database into temporary tables, and then write scripts to import this into the target database with hand-written SQL, starting at the "furthest" end of the dependency chain. Quite laborious, though.