防止自动递增整数主键? [英] 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 of PRIMARY KEY.
• Declare the column type as INT instead of INTEGER.

请注意，任何一个都会为您提供一个具有整数 affinity 的列，而不是被约束为仅包含整数.

Note that either one will give you a column with integer affinity instead of being constrained to contain only integers.

这篇关于防止自动递增整数主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！