防止自动递增整数主键? [英] prevent autoincrementing integer primary key?
问题描述
我有一个sqlite表(sqlite版本3.7.3),其中插入主键列的空值会不希望地自动递增:
I have a sqlite table (sqlite version 3.7.3) where nulls inserted into the primary key column are being undesirably auto-incremented:
sqlite> CREATE TABLE foo(bar INTEGER NOT NULL PRIMARY KEY);
sqlite> INSERT INTO foo(bar) VALUES(NULL);
sqlite> SELECT * FROM foo;
1
在 sqlite文档中,它表明将AUTOINCREMENT
关键字添加到该列中应创建这种行为,但是似乎没有防止自动递增...
In the sqlite docs, it shows that adding the AUTOINCREMENT
keyword to the column should create this behavior, but there doesn't appear to be a keyword to prevent the auto incrementing...
我还发现我可以使用SQLITE_OMIT_AUTOINCREMENT
编译选项构建sqlite,但我不想这样做仅针对此特定列,全局禁用该行为.
I also found that I can build sqlite with the SQLITE_OMIT_AUTOINCREMENT
compile option, but I don't want to disable the behavior globally, just for this particular column.
有趣的是,如果不包含PRIMARY KEY
约束,则会得到所需的行为:
Interestingly, if I don't include the PRIMARY KEY
constraint, I get the desired behavior:
sqlite> CREATE TABLE FOO(bar integer NOT NULL);
sqlite> INSERT INTO FOO(bar) VALUES(NULL);
SQL error: foo.bar may not be NULL
如何定义表,以便拒绝NULL值并保持主键约束?
How can I define the table so that NULL values are rejected and keep the primary key constraint?
推荐答案
自动增量行为仅适用于声明为INTEGER PRIMARY KEY
.因此,禁用它的最简单方法是:
Autoincrement behavior applies only to columns declared as INTEGER PRIMARY KEY
. So the easiest ways to disable it are:
- 将该列声明为
UNIQUE
而不是PRIMARY KEY
. - 将列类型声明为
INT
而不是INTEGER
.
- Declare the column as
UNIQUE
instead ofPRIMARY KEY
. - Declare the column type as
INT
instead ofINTEGER
.
请注意,任何一个都会为您提供一个具有整数 affinity 的列,而不是被约束为仅包含整数.
Note that either one will give you a column with integer affinity instead of being constrained to contain only integers.
这篇关于防止自动递增整数主键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!