向现有 SQLite 表添加约束 [英] Add constraint to existing SQLite table

问题描述

我正在使用 SQLite，它不支持添加 约束到现有表.

I'm using SQLite, which doesn't support adding a constraint to an existing table.

所以我不能做这样的事情(仅作为示例):

So I can't do something like this (just as an example):

ALTER TABLE [Customer]
ADD CONSTRAINT specify_either_phone_or_email
CHECK (([Phone] IS NOT NULL) OR ([Email] IS NOT NULL));

这种情况有什么解决方法吗?

我知道:

• 我可以为新表添加约束，但它不是新的(它是由我的 ORM、EF Core 生成的)
• 我可以进行表重建"(重命名表、创建新表、复制旧数据、删除临时表)，但这看起来非常复杂

想法

• 我能否以某种方式将该表的副本复制到一个新表中，并对架构进行一些更改?
• 或者以某种方式获取"架构，然后在 SQL 脚本中对其进行编辑，然后添加具有该架构的表?

推荐答案

要复制一些架构更改的表，您必须手动创建和复制:

To make a copy of a table with some schema changes, you have to do the creation and the copying manually:

BEGIN;
CREATE TABLE Customer_new (
    [...],
    CHECK ([...])
);
INSERT INTO Customer_new SELECT * FROM Customer;
DROP TABLE Customer;
ALTER TABLE Customer_new RENAME TO Customer;
COMMIT;

<小时>

要读取架构，请在 sqlite3 中执行 .schema Customer 命令行外壳.这为您提供了 CREATE TABLE 语句，您可以对其进行编辑和执行.

---

To read the schema, execute .schema Customer in the sqlite3 command-line shell. This gives you the CREATE TABLE statement, which you can edit and execute.

要就地更改表格，您可以使用后门.

To change the table in place, you can use a backdoor.

首先，阅读实际的表定义(这与您从 .schema 中得到的相同):

First, read the actual table definition (this is the same as what you would get from .schema):

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'Customer';

将您的 CHECK 约束添加到该字符串，然后使用 sqlite_master 的写访问">PRAGMA writable_schema=1; 并将您的新表定义写入其中:

Add your CHECK constraint to that string, then enable write access to sqlite_master with PRAGMA writable_schema=1; and write your new table definition into it:

UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='Customer';

然后重新打开数据库.

警告:这仅适用于不会更改表的磁盘格式的更改.如果您确实进行了更改记录格式的任何更改(例如添加/删除字段，或修改 rowid，或添加需要内部索引的约束)，您的数据库将会爆炸.

WARNING: This works only for changes that do not change the on-disk format of the table. If you do make any change that changes the record format (such as adding/removing fields, or modifying the rowid, or adding a constraint that needs an internal index), your database will blow up horribly.

这篇关于向现有 SQLite 表添加约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！