SQL表中主键ID之间的间隙 [英] Gaps between primary key id in sql table
问题描述
我有一个表，该表是：
CREATE SEQUENCE id_seq;
CREATE TABLE public."UserInfo"
(
id bigint NOT NULL DEFAULT nextval('id_seq'),
phone text,
password text,
name text,
surname text,
middle_name text,
email text,
company text,
title text,
image_id text,
CONSTRAINT "UserInfo_pkey" PRIMARY KEY (id),
CONSTRAINT "UserInfo_image_id_key" UNIQUE (image_id),
CONSTRAINT "UserInfo_phone_key" UNIQUE (phone)
)
WITH (
OIDS=FALSE
);
ALTER SEQUENCE id_seq OWNED BY public."UserInfo".id;
ALTER TABLE public."UserInfo"
OWNER TO postgres;
当我提出错误的请求时，例如唯一列的值相同。 id正在增加...这是错误的id请求；
When I make bad request for insert like same value for unique column. "id" is increasing... Here is bad id request;
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 9921455867, mg123209, name, surname, , namesurname@xxx.com, Company Name, Title Of Person, 123asd).
********** Error **********
这是我的表结果；
1;"1234477867";"qweff";"Name";"Surname";"''";"namesurname@qwe.com";"Company";"Title";"qwer1234"
4;"5466477868";"1235dsf";"Name";"Surname";"''";"banesyrna@pwqe.com";"Company";"Title";"qwer1235"
6;"5051377828";"asd123";"Name";"Surname";"''";"qweg@sdcv.com";"Company";"Title";"qwesr1235"
请帮助我如何解决此问题，我要订购例如 1,2,3。。
Please help me how I can solve this issue, I want order like 1,2,3.. sequential..
推荐答案
这是序列。
重要：避免阻塞并发从相同序列中获得
个数字的交易，nextval操作永远不会回滚
；也就是说，一旦获取了值，就将其视为已使用，并且
将不再返回。即使周围的
事务稍后中止，或者调用查询最终不使用
值，也是如此。
Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value.
正如评论中指出的那样，序列中的间隔没有任何危害。如果出于某种原因删除表中的某些行，则会在主键值中造成缺口，并且通常不会为将它们重置为连续而烦恼。
As pointed out in the comments there's no harm in having gaps in sequences. If you delete some rows in your table for what ever reason you are creating gaps in your primary key values and you wouldn't normally bother with resetting them to make them sequential.
如果您坚持要创建无缝序列，请阅读此文章： http://www.varlena.com /GeneralBits/130.php ，并准备好慢速插入。
If you insist on creating a gapless sequence, read this article: http://www.varlena.com/GeneralBits/130.php and be prepared for slow inserts.
这篇关于SQL表中主键ID之间的间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
