作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试根据 this answer 删除表上的非空约束.但是,它似乎没有在 sqlite_sequence
中创建条目。这样做之后,即使我可以在使用测试表时让它正常工作。 有趣的是,如果我备份我的表,重新创建它,在其中插入两个假行,然后重复上述过程,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);
The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.
post
表,然后我重新创建它,然后在其中添加两个假行,然后重复上述过程,
sqlite_sequence
表是正确的。我的原始数据集似乎有问题,但我不确定如何调试该问题。
最佳答案
对于表格的声明:
INSERT INTO this SELECT * FROM that;
foo
)时,总是可以使用这种优化。 sqlite_sequence
table在停止程序之前。
关于Sqlite3 : Creating a new table from an old table is not populating the sqlite_sequence table correctly,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39820552/
我是一名优秀的程序员,十分优秀!