sqlite - 通过将 SQLite 表拆分为两部分并与外键链接来重构 SQLite 表

Question

我正在开发一个 SQLite 数据库。数据库已经填满了，但我想重构它。这是我需要做的一个示例:

我目前有一张 table :

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 内容的表 table :

CREATE TEMPORARY TABLE Cars_temp AS SELECT * FROM Cars;

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

但我仍在寻找一种方法来检查相同的选择，同时将 EngineCap字段进入新的 Cars表和 以某种方式从 Vehicles 中提取相应的 ID 值表放入VehicleID Cars 上的外键字段 table 。

我愿意接受解决方法或替代方法。

谢谢。

Answer 1

由于@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])
);

(对于这个例子，我在同一个数据库中进行转换。您可能会使用两个 DB，将一个转换为另一个，使用 SQL ATTACH 命令。)

现在我们创建一个 View (它使用新表模仿我们的原始表)和触发器:

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 而不是(未执行的)插入操作中的 rowid。

这是一个完整的解决方案。它管理一个音乐记录数据库，由歌曲名称、专辑名称和艺术家姓名组成。

-- 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，但不幸的是，它没有( see this related question)。