在两个与外键链接它拆分重构SQLite表(Refactor SQLite Table by spli

我工作的一个SQLite数据库。该数据库已填满，但我想重构它。这里是什么，我需要做的一个样本：

我现在有一个表：

CREATE TABLE Cars (ID INTEGER PRIMARY KEY,
                   Name VARCHAR(32),
                   TopSpeed FLOAT,                   
                   EngineCap FLOAT);

我想这个分成两个表：

CREATE TABLE Vehicles (ID INTEGER PRIMARY KEY,
                       Name VARCHAR(32),
                       TopSpeed FLOAT); 

CREATE TABLE Cars (ID INTEGER PRIMARY KEY,
                   VehicleID INTEGER CONSTRAINT FK_Cars REFERENCES [Vehicles](ID),                  
                   EngineCap FLOAT);

我已经想通了，以创建一个临时表Cars表格内容，我可以填补Vehicles用的目录Cars表：

CREATE TEMPORARY TABLE Cars_temp AS SELECT * FROM Cars;

INSERT INTO Vehicles (Name, TopSpeed)
SELECT Name, TopSpeed FROM Cars_temp;

但我仍然在寻找一种方式去在同样的选择，而把EngineCap场到新Cars的表， 不知何故从提取相应的ID值Vehicles表装进VehicleID外键字段上Cars表。

我很开放的解决办法或替代办法。

谢谢。

Answer 1:

没有触发简单的解决方案：

创建VEHICLES_TEMP表包括CAR_ID
创建新的CARS表没有你不想要的车辆列
与VEHICLE_ID更新CARS从VEHICLES_TEMP采取（由CAR_ID标识）
创建最终的车辆表没有CAR_ID

Answer 2:

创建一个表New_Cars和INSTEAD OF INSERT触发器，从而将数据插入到两个表Vehicles和Cars 。当插入到Cars ，你可以使用last_insert_rowid（）函数来引用插入行Vehicles表。

这可能是治标不治本，或者你可以把它留在你的数据库进行进一步的修改。

Answer 3:

由于@mateusza没有提供一个例子，我做了一个：

假设你有这个表：

CREATE TABLE [Customer] (
  [name] TEXT,
  [street] TEXT,
  [city] TEXT);

现在，你要移动street和city成一个单独的表Address ，所以你会用两个表结束：

CREATE TABLE [Customer2] (
  [name] TEXT,
  [addr] INTEGER);

CREATE TABLE [Address] (
  [rowid] INTEGER NOT NULL,
  [street] TEXT,
  [city] TEXT,
  PRIMARY KEY ([rowid])
);

（在这个例子中，我做的转换在同一个数据库中。你可能会使用两个数据块，转换到彼此，与SQL ATTACH命令。）

现在，我们创建一个视图，触发器（使用新表模仿我们的原始表）：

CREATE VIEW Customer1 (name, street, city) AS
    SELECT C.name, A.street, A.city FROM Customer2 AS C
    JOIN Address as A ON (C.addr == A.rowid);

CREATE TEMP TRIGGER TempTrig INSTEAD OF INSERT ON Customer1 FOR EACH ROW BEGIN
    INSERT INTO Address (street, city) SELECT NEW.street, NEW.city;
    INSERT INTO Customer2 (addr, name) SELECT last_insert_rowid(), NEW.name;
END;

现在，您可以复制表行：

INSERT INTO Customer1 (name, street, city) SELECT name, street, city FROM Customer;

以上是一个简化的情况下你只有一些数据移动到一个新表。

一个更复杂的（更普遍）的情况是，你要...

分开你的原始表中的列到几个外国表，
有外国表中的唯一条目（这通常是为什么你会重构你的表的原因）。

这增加了一些额外的挑战：

你最终会插入到多个表，然后才能插入他们的rowid到表与引用的rowid。这要求每一个的结果存储INSERT的last_insert_rowid（）到临时表。
如果该值在国外表已经存在，其ROWID必须被存储，而不是从（非执行）插入操作的一个。

下面是这个完整的解决方案。它管理的音乐记录的数据库，constisting一首歌曲的名称，专辑名称和艺术家的名字。

-- Original table
CREATE TABLE [Song] (
  [title] TEXT,
  [album] TEXT,
  [artist] TEXT
);

-- Refactored tables
CREATE TABLE [Song2] (
  [title] TEXT,
  [album_rowid] INTEGER,
  [artist_rowid] INTEGER
);
CREATE TABLE [Album] (
  [rowid] INTEGER PRIMARY KEY AUTOINCREMENT,
  [title] TEXT UNIQUE
);
CREATE TABLE [Artist] (
  [rowid] INTEGER PRIMARY KEY AUTOINCREMENT,
  [name] TEXT UNIQUE
);

-- Fill with sample data

INSERT INTO Song VALUES ("Hunting Girl", "Songs From The Wood", "Jethro Tull");
INSERT INTO Song VALUES ("Acres Wild", "Heavy Horses", "Jethro Tull");
INSERT INTO Song VALUES ("Broadford Bazar", "Heavy Horses", "Jethro Tull");
INSERT INTO Song VALUES ("Statue of Liberty", "White Music", "XTC");
INSERT INTO Song VALUES ("Standing In For Joe", "Wasp Star", "XTC");
INSERT INTO Song VALUES ("Velvet Green", "Songs From The Wood", "Jethro Tull");

-- Conversion starts here

CREATE TEMP TABLE [TempRowIDs] (
  [album_id] INTEGER,
  [artist_id] INTEGER
);

CREATE VIEW Song1 (title, album, artist) AS
  SELECT Song2.title, Album.title, Artist.name
    FROM Song2
    JOIN Album ON (Song2.album_rowid == Album.rowid)
    JOIN Artist ON (Song2.artist_rowid == Artist.rowid);

CREATE TEMP TRIGGER TempTrig INSTEAD OF INSERT ON Song1 FOR EACH ROW BEGIN
  INSERT OR IGNORE INTO Album (title) SELECT NEW.album;
  UPDATE TempRowIDs SET album_id = (SELECT COALESCE (
    (SELECT rowid FROM Album WHERE changes()==0 AND title==NEW.album), last_insert_rowid()
  ) ) WHERE rowid==1;
  INSERT OR IGNORE INTO Artist (name) SELECT NEW.artist;
  UPDATE TempRowIDs SET artist_id = (SELECT COALESCE (
    (SELECT rowid FROM Artist WHERE changes()==0 AND name==NEW.artist), last_insert_rowid()
  ) ) WHERE rowid==1;
  INSERT INTO Song2 (title, album_rowid, artist_rowid) SELECT
    NEW.title, (SELECT album_id FROM TempRowIDs), (SELECT artist_id FROM TempRowIDs);
END;

INSERT INTO TempRowIDs DEFAULT VALUES;

INSERT INTO Song1 (title, album, artist) SELECT title, album, artist FROM Song;

DROP TRIGGER TempTrig;
DROP TABLE TempRowIDs;

-- Conversion ends here

-- Print results
SELECT * FROM Song;
SELECT * FROM Song1;

-- Check if original and copy are identical (https://stackoverflow.com/a/13865679/43615)
SELECT CASE WHEN (SELECT COUNT(*) FROM (SELECT * FROM Song UNION SELECT * FROM Song1)) == (SELECT COUNT() FROM Song) THEN 'Success' ELSE 'Failure' END;

注意，这个例子有一个潜在的问题：如果在国外表上的约束都比较复杂， SELECT rowid FROM搜索现存的录入需要进行相应的更新。理想情况下，SQLite的应该提供一种方法以某种方式来确定冲突的rowid，但它不，不幸的是（见此相关的问题）。