时间戳

首页 > 解决方案 > 重新创建父表后如何在 SQLite 中重新设置表 FOREIGN KEY

sqlite - 重新创建父表后如何在 SQLite 中重新设置表 FOREIGN KEY

问题描述

考虑我的汽车租赁公司，有两种不同的租赁车辆：

CREATE TABLE busses (
    id INTEGER PRIMARY KEY NOT NULL,
    places INTEGER NOT NULL
);
CREATE TABLE cars (
    id INTEGER PRIMARY KEY NOT NULL,
    places INTEGER NOT NULL
);

CREATE TABLE vehicles (
    id INTEGER PRIMARY KEY NOT NULL,
    bus_id INTEGER,
    car_id INTEGER,

    FOREIGN KEY(bus_id) REFERENCES busses(id),
    FOREIGN KEY(car_id) REFERENCES cars(id),

    CHECK (NOT(bus_id IS NULL AND car_id IS NULL))
);

CREATE TABLE rentals(
    id INTEGER PRIMARY KEY NOT NULL,
    vehicle_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,

    FOREIGN KEY(vehicle_id) REFERENCES vehicles(id)
);

我的汽车公司过去几年做得很好。我们已经订购了几辆卡车，我们也计划将它们出租。我们想要得到这个模式：

CREATE TABLE busses (..);
CREATE TABLE cars (..);
CREATE TABLE trucks (
    id INTEGER PRIMARY KEY NOT NULL,
    tar_weight INTEGER NOT NULL,
    max_net_weight INTEGER NOT NULL
);

CREATE TABLE vehicles (
    id INTEGER PRIMARY KEY NOT NULL,
    bus_id INTEGER,
    car_id INTEGER,
    truck_id INTEGER,

    FOREIGN KEY(bus_id) REFERENCES busses(id),
    FOREIGN KEY(car_id) REFERENCES cars(id),
    FOREIGN KEY(truck_id) REFERENCES trucks(id),

    CHECK (NOT(bus_id IS NULL AND car_id IS NULL AND truck_id IS NULL))
);

CREATE TABLE rentals(...);

在普通 SQL 中，您可以使用ALTER TABLE. 但是，SQLite 似乎不允许重写CHECK约束（例如，我如何从 sqlite (3.6.21) 表中删除约束？），也不允许删除它并重新添加它。

解决方案是

  1. 重命名表格；然后
  2. CHECK使用新约束重新创建新表；然后
  3. 重新插入旧表中的数据。

就像是

ALTER TABLE vehicles RENAME TO vehicles_old;
CREATE TABLE vehicles (...);
INSERT INTO vehicles (id, bus_id, car_id) SELECT * FROM vehicles_old;
DROP TABLE vehicles_old;

但是，将vehicles表重命名为vehicles_old也会重命名FOREIGN KEY附带的约束，busses并且cars：它们在重命名后指向vehicles_old表，如下所示：

sqlite> select * from sqlite_schema;
table|busses|busses|2|CREATE TABLE busses (
    id INTEGER PRIMARY KEY NOT NULL,
    places INTEGER NOT NULL
)
table|cars|cars|3|CREATE TABLE cars (
    id INTEGER PRIMARY KEY NOT NULL,
    places INTEGER NOT NULL
)
table|rentals|rentals|5|CREATE TABLE rentals(
    id INTEGER PRIMARY KEY NOT NULL,
    vehicle_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,

    FOREIGN KEY(vehicle_id) REFERENCES "vehicles_old"(id)
)
table|trucks|trucks|6|CREATE TABLE trucks (
    id INTEGER PRIMARY KEY NOT NULL,
    tar_weight INTEGER NOT NULL,
    max_net_weight INTEGER NOT NULL
)
table|vehicles|vehicles|7|CREATE TABLE vehicles (
    id INTEGER PRIMARY KEY NOT NULL,
    bus_id INTEGER,
    car_id INTEGER,
    truck_id INTEGER,

    FOREIGN KEY(bus_id) REFERENCES busses(id),
    FOREIGN KEY(car_id) REFERENCES cars(id),
    FOREIGN KEY(truck_id) REFERENCES trucks(id),

    CHECK (NOT(bus_id IS NULL AND car_id IS NULL AND truck_id IS NULL))
)

我现在的问题是：我如何重新设置rentals表的父级，使其指向vehicles表而不是不存在的vehicles_old表？

注意，这可能被认为是一个 xy 问题，其中“y”是“重新设置rentals?”，而“x”是“我如何更新vehicles的检查约束？”。我认为后者是不可能的。

这不应该被认为是一个 xyz 问题，我应该介绍一个vehicle_types和vehicle_attributes表；在我的真实案例中，我们真的在谈论 ISA 关系。

标签： sqlitedatabase-designdatabase-migration

解决方案

当然，在发布 20 分钟后，您会意识到过去一个小时里答案一直盯着您。

从ALTER TABLE的“2. ALTER TABLE RENAME”部分：

在版本 3.26.0 (2018-12-01) 之前，仅当 PRAGMA foreign_keys=ON 或换句话说，如果强制执行外键约束时，才会编辑对重命名表的 FOREIGN KEY 引用。使用 PRAGMA foreign_keys=OFF，当外键引用的表（“父表”）被重命名时，FOREIGN KEY 约束不会改变。从版本 3.26.0 开始，重命名表时始终转换 FOREIGN KEY 约束，除非使用了 PRAGMA legacy_alter_table=ON 设置。

虽然我尝试了两种 PRAGMA 的多种组合，但我没有意识到这PRAGMA foreign_key=OFF; 是在 transaction 期间无操作。当然，我在启用了 FK 检查的事务中运行我的迁移。如果我们不运行古老的 SQLite 3.15.2，这将不是问题，因为那里PRAGMA legacy_alter_table=ON;已经足够了。

推荐阅读