SQLite3的primary key可以为null,但有个例外

2020年10月27日 / 8次阅读 / Last Modified 2020年10月27日
SQLite

做了个测试,发现sqlite3表中的primary key是可以为NULL的,而且还可以多个NULL(NULL值不受primary key自带的unique约束)。

因此,在sqlite3中创建表的时候,主键建议一定加上 not null 约束!

create table tname(id int primary key not null, ....);

在一本书中看到如下介绍:

In SQLite, PRIMARY KEY does not imply NOT NULL . This is in contradiction to the SQL standard and is considered a bug, but the behavior is so long-standing that there are concerns about fixing it and breaking existing applications. As a result, it is always a good idea to explicitly mark at least one column from each PRIMARY KEY as NOT NULL.

Primary Key可以为Null,既不符合SQL标准,也被认为是个bug,但是由于SQLite非常广泛的应用,修复这个问题可能导致很多app崩溃。

有一个例外!

SQLite想其它数据库一样,会有个默认的不可见的ROWID列。如果你的table没有primary key,有重复的row数据,SQLite就要用ROWID列来对这些数据进行区分。

如果table中有 INTEGER PRIMARY KEY,那么SQLite就会直接将这一列与ROWID合并。虽然没有 NOT NULL 的 column constraint,此时 INSERT INTO ... NULL 数据不会错,但是这一列会默认保存为 INTEGER,并且 有AUTOINCREMENT。

sqlite> create table aa(id integer primary key);
sqlite> insert into aa values (null);
sqlite> insert into aa values (null);
sqlite> insert into aa values (null);
sqlite> insert into aa values (null);
sqlite> insert into aa values (null);
sqlite> select * from aa;
1
2
3
4
5

对于SQLite而言,INT 和 INTEGER 是不一样的。如果在 CREATE TABLE 的时候,类型写成了 INT,就没有这个效果! INT PRIMARY KEY 被称为 ROWID Alias列。

因此这样设计有额外的好处:

  • SQLite不再创建额外的ROWID列,有助于运行效率的提高;
  • INTEGER PRIMARY KEY 自动有了 NOT NULL UNIQUE 约束。

INTEGER PRIMARY KEY 不自带 AUTOINCREMENT 约束,这一列会增加,也有可能重复利用被删除的数字,如果带上 AUTOINCREMENT 约束,区别就是不会重复利用被删除的数字,直到触及最大值,这几乎是不可能的。

-- EOF --

本文链接:https://www.pynote.net/archives/2661

留言区

《SQLite3的primary key可以为null,但有个例外》有1条留言

电子邮件地址不会被公开。 必填项已用*标注

  • 麦新杰

    rowid是可以被select的:

    sqlite> select * from test;
    sn  seq
    --  ---
    1   1
    1   1
    1   1
    1   1
    1   1
    sqlite> select rowid from test;
    rowid
    -----
    1
    2
    3
    4
    5
    
     [回复]


前一篇:
后一篇:

More


©Copyright 麦新杰 Since 2019 Python笔记

go to top