Sqlite3：从旧表创建一个新表不是正确填充sqlite_sequence表

问题描述：

我试图根据this answer删除表上的一个非空约束。不过，这样做似乎并没有在sqlite_sequence中创建条目，即使我可以在使用测试表时正常工作。 有趣的是，如果我备份了我的表，重新创建它，在其中插入两个假行，然后重复上述步骤，sqlite_sequence表正确填充。但是当我使用原始数据集时，该过程无法正常工作。Sqlite3：从旧表创建一个新表不是正确填充sqlite_sequence表

例如，这工作正常上一个测试表：

CREATE TABLE foo (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    bar VARCHAR NOT NULL 
); 
INSERT INTO foo (bar) VALUES ('foo'); 
INSERT INTO foo (bar) VALUES ('bar'); 

-- As expected, this shows foo | 2 
SELECT * FROM sqlite_sequence WHERE name = 'foo'; 

BEGIN TRANSACTION; 
ALTER TABLE foo RENAME TO temp_foo; 
CREATE TABLE foo (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    bar VARCHAR 
); 
INSERT INTO foo SELECT * from temp_foo; 

-- As expected, this shows foo | 2 
SELECT * FROM sqlite_sequence WHERE name = 'foo'; 
COMMIT;

然而，当我做我的真表完全一样的命令，它没有一个条目添加到sqlite_sequence。

sqlite> .schema post; 
CREATE TABLE post (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    user_id INTEGER NOT NULL, 
    title VARCHAR NOT NULL, 
    url_name VARCHAR NOT NULL, 
    description VARCHAR NOT NULL, 
    category_id INTEGER NOT NULL, 
    content VARCHAR, 
    is_published BOOLEAN, 
    creation_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, 
    last_modified_date DATETIME DEFAULT CURRENT_TIMESTAMP, 
    is_commenting_disabled BOOLEAN NOT NULL, 
    CHECK (title <> ''), 
    CHECK (url_name <> ''), 
    CHECK (description <> ''), 
    CHECK (content <> ''), 
    FOREIGN KEY(user_id) REFERENCES user (id) ON DELETE CASCADE, 
    UNIQUE (url_name), 
    FOREIGN KEY(category_id) REFERENCES category (id) ON DELETE CASCADE, 
    CHECK (is_published IN (0, 1)), 
    CHECK (is_commenting_disabled IN (0, 1)) 
); 
sqlite> 
sqlite> select * from sqlite_sequence where name = 'post'; 
post|114 
sqlite> 
sqlite> BEGIN TRANSACTION; 
sqlite> 
sqlite> ALTER TABLE post RENAME TO temp_post; 
sqlite> 
sqlite> CREATE TABLE post (
    ...>   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    ...>   user_id INTEGER NOT NULL, 
    ...>   title VARCHAR NOT NULL, 
    ...>   url_name VARCHAR NOT NULL, 
    ...>   description VARCHAR, 
    ...>   category_id INTEGER NOT NULL, 
    ...>   content VARCHAR, 
    ...>   is_published BOOLEAN, 
    ...>   creation_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, 
    ...>   last_modified_date DATETIME DEFAULT CURRENT_TIMESTAMP, 
    ...>   is_commenting_disabled BOOLEAN NOT NULL, 
    ...>   CHECK (title <> ''), 
    ...>   CHECK (url_name <> ''), 
    ...>   CHECK (description <> ''), 
    ...>   CHECK (content <> ''), 
    ...>   FOREIGN KEY(user_id) REFERENCES user (id) ON DELETE CASCADE, 
    ...>   UNIQUE (url_name), 
    ...>   FOREIGN KEY(category_id) REFERENCES category (id) ON DELETE CASCADE, 
    ...>   CHECK (is_published IN (0, 1)), 
    ...>   CHECK (is_commenting_disabled IN (0, 1)) 
    ...>); 
sqlite> 
sqlite> INSERT INTO post SELECT * FROM temp_post; 
sqlite> 
sqlite> select * from sqlite_sequence WHERE name in ('temp_post', 'post'); 
temp_post|114 
sqlite> COMMIT; 
sqlite> 
sqlite> select * from sqlite_sequence WHERE name in ('temp_post', 'post'); 
temp_post|114

我已经这样做了大约三次，我无法得到它的工作。最后我做一个

INSERT INTO sqlite_sequence VALUES ('post', 114);

，一切似乎是工作的罚款

但当然，文件有这样一段话：

的sqlite_sequence表的内容可以通过修改普通的UPDATE，INSERT和DELETE语句。但是对该表进行修改可能会干扰AUTOINCREMENT密钥生成算法。在进行这样的改变之前，确保你知道你在做什么。

正如前面提到的，如果我备份post表，我重新创建它，然后在里面添加了两个假的行，然后重复上述步骤后，sqlite_sequence表是否正确。这似乎是我的原始数据集有问题，但我不知道如何调试问题。

@CL。这很好，你会发布这个答案吗？ –

@CL。此外，任何想法，为什么这个工程时，我插入虚假的行，而不是工作的旧数据？ –

答

对于形式的语句：

INSERT INTO this SELECT * FROM that;

SQLite的具有可复制整个行，而无需解码和编码列值的专项转移优化。

当表格结构简单时（例如foo），可始终使用此优化。
某些功能（如外键约束）与此优化不兼容。
而在某些情况下，例如存在UNIQUE约束时，只有目标表为空时，此优化才有效。

在第三种情况下，为该语句生成的代码检查该表是否为空。如果它是空的，传输优化完成，程序停止;如果它不是空的，它跳转到正常执行读/写操作的第二部分。但是，在停止程序之前执行空表检查forgot to update the sqlite_sequence table的代码。

作为解决方法，通过启用外键检查将其移入第二种情况。

This bug在3.6.16和was fixed in version 3.15.0版本中引入的。

Sqlite3：从旧表创建一个新表不是正确填充sqlite_sequence表

相关推荐