如何在SQLite中添加默认值?

时间:2022-06-01 21:40:03

I had a table modified to add status column to it in this fashion

我修改了一个表,以这种方式添加状态栏。

ALTER TABLE ITEM ADD COLUMN STATUS VARCHAR DEFAULT 'N';

However SQLite doesnt seem to add N to the column for any new ITEM created. Is the syntax wrong or is there any issue with SQLite and its support for defaults.

然而,对于创建的任何新项目,SQLite似乎都没有在列中添加N。是否语法错误,或者是否存在SQLite的问题以及对默认值的支持。

I am using SQLite 3.6.22

我使用的是SQLite 3.6.22

1 个解决方案

#1


39  

Looks good to me. Here are the Docs.

我看起来很好。这是文档。

sqlite> create table t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE);
sqlite> .table
t1
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE);
COMMIT;

sqlite> alter table t1 add column status varchar default 'N';
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE, status varchar default 'N');
COMMIT;

sqlite> insert into t1 (name) values ("test");
sqlite> select * from t1;
1|test||N

Dump your schema and verify that your table structure is there after calling ALTER TABLE but before the INSERT. If it's in a transaction, make sure to COMMIT the transaction before the insert.

转储模式,并在调用ALTER table之后,但在插入之前验证表结构是否存在。如果它在事务中,请确保在插入之前提交事务。

$ sqlite3 test.db ".dump"

#1


39  

Looks good to me. Here are the Docs.

我看起来很好。这是文档。

sqlite> create table t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE);
sqlite> .table
t1
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE);
COMMIT;

sqlite> alter table t1 add column status varchar default 'N';
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (id INTEGER PRIMARY KEY, name TEXT, created DATE, status varchar default 'N');
COMMIT;

sqlite> insert into t1 (name) values ("test");
sqlite> select * from t1;
1|test||N

Dump your schema and verify that your table structure is there after calling ALTER TABLE but before the INSERT. If it's in a transaction, make sure to COMMIT the transaction before the insert.

转储模式,并在调用ALTER table之后,但在插入之前验证表结构是否存在。如果它在事务中,请确保在插入之前提交事务。

$ sqlite3 test.db ".dump"