Trung tâm cá nhân
sách gpt4 ai đã đi

Cấu trúc lại bảng SQLite bằng cách chia nó thành hai phần và liên kết với khóa ngoại

In lại 作者：行者123 更新时间：2023-12-03 17:54:55 34 4
mua khóa gpt4 Nike

我正在开发一个 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 创建一个临时表表内容，我可以填写 VehiclesBao gồm 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 。

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

Cảm ơn.

1 Câu trả lời

由于@mateusza 没有提供示例，因此我做了一个示例:

假设你有这张表:

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

现在你要搬家 streetVà 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).

    关于sqlite - 通过将 SQLite 表拆分为两部分并与外键链接来重构 SQLite 表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10849411/

    34 4 0
    Bài viết được đề xuất: sqlite - 在Windows 8和SQLite Metro中使用我自己的数据库
    Bài viết được đề xuất: python - 使用 sqlite3 db 缺少表的单元测试 django 项目
    Bài viết được đề xuất: Python/sqlite : get column values as a list/tuple from rows where other columns have certain values
    Bài viết được đề xuất: javascript - 在 html5 canvas 和 box2d 中跟随 body
    行者123
    Hồ sơ cá nhân

    Tôi là một lập trình viên xuất sắc, rất giỏi!

    Nhận phiếu giảm giá Didi Taxi miễn phí
    Mã giảm giá Didi Taxi
    Các bài viết nóng hổi trên toàn bộ trang web
    Giấy chứng nhận ICP Bắc Kinh số 000000
    Hợp tác quảng cáo: 1813099741@qq.com 6ren.com