通过将SQLite表一分为二并与外键链接来重构 [英] Refactor SQLite Table by splitting it in two and link with foreign keys

问题描述

我正在使用SQLite数据库.该数据库已经填充，但是我想对其进行重构.这是我需要做的一个例子:

I'm working on a SQLite Database. The database is already filled, but I want to refactor it. Here is a sample of what I need to do:

我目前有一张桌子:

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表内容创建一个临时表的方法，我可以用Cars表的内容填充Vehicles表:

I have figured out to create a temporary table with the Cars table contents, and I can fill up the Vehicles table with the contents of the 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值以放入进入Cars表的VehicleID外键字段.

But I am still looking for a way to go over that same selection, while putting the EngineCap field into the new Cars table and somehow extracting the corresponding ID value from the Vehicles table to put into the VehicleID foreign key field on the Cars table.

我愿意接受变通方法或替代方法.

I'm open for workaround or alternative approaches.

谢谢.

推荐答案

没有触发器的简单解决方案:

Simple solution without triggers:

• 创建包含CAR_ID的VEHICLES_TEMP表
• 创建新的CARS表，其中不包含不需要的VEHICLES列
• 使用从VEHICLES_TEMP(由CAR_ID标识)中提取的VEHICLE_ID更新CARS
• 创建没有CAR_ID的最终VEHICLES表

这篇关于通过将SQLite表一分为二并与外键链接来重构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！