我有一个名为客户端的表，它依赖于其他表的输入。具有以下规格：
crewdb=#\d+ client;
Table "public.client"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------------+---------+-----------+----------+------------------------------+----------+--------------+-------------
clientid | integer | | not null | generated always as identity | plain | |
account_name | text | | not null | | extended | |
last_name | text | | | | extended | |
first_name | text | | | | extended | |
address | text | | not null | | extended | |
suburbid | integer | | | | plain | |
cityid | integer | | | | plain | |
post_code | integer | | not null | | plain | |
business_phone | text | | | | extended | |
home_phone | text | | | | extended | |
mobile_phone | text | | | | extended | |
alternative_phone | text | | | | extended | |
email | text | | | | extended | |
quote_detailsid | integer | | | | plain | |
invoice_typeid | integer | | | | plain | |
payment_typeid | integer | | | | plain | |
job_typeid | integer | | | | plain | |
communicationid | integer | | | | plain | |
accessid | integer | | | | plain | |
difficulty_levelid | integer | | | | plain | |
current_lawn_price | numeric | | | | main | |
square_meters | numeric | | | | main | |
note | text | | | | extended | |
client_statusid | integer | | | | plain | |
reason_for_statusid | integer | | | | plain | |
Indexes:
"client_pkey" PRIMARY KEY, btree (clientid)
"account_name_check" UNIQUE CONSTRAINT, btree (account_name)
Foreign-key constraints:
"client_accessid_fkey" FOREIGN KEY (accessid) REFERENCES access(accessid)
"client_cityid_fkey" FOREIGN KEY (cityid) REFERENCES city(cityid)
"client_client_statusid_fkey" FOREIGN KEY (client_statusid) REFERENCES client_status(client_statusid)
"client_communicationid_fkey" FOREIGN KEY (communicationid) REFERENCES communication(communicationid)
"client_difficulty_levelid_fkey" FOREIGN KEY (difficulty_levelid) REFERENCES difficulty_level(difficulty_levelid)
"client_invoice_typeid_fkey" FOREIGN KEY (invoice_typeid) REFERENCES invoice_type(invoice_typeid)
"client_job_typeid_fkey" FOREIGN KEY (job_typeid) REFERENCES job_type(job_typeid)
"client_payment_typeid_fkey" FOREIGN KEY (payment_typeid) REFERENCES payment_type(payment_typeid)
"client_quote_detailsid_fkey" FOREIGN KEY (quote_detailsid) REFERENCES quote_details(quote_detailsid)
"client_reason_for_statusid_fkey" FOREIGN KEY (reason_for_statusid) REFERENCES reason_for_status(reason_for_statusid)
"client_suburbid_fkey" FOREIGN KEY (suburbid) REFERENCES suburb(suburbid)
Referenced by:
TABLE "work" CONSTRAINT "work_clientid_fkey" FOREIGN KEY (clientid) REFERENCES client(clientid)
由于客户记录被删除，因此 clientid 列（主键）的顺序混乱，如下所示：
crewdb=# select clientid from client order by clientid asc limit 22;
clientid
----------
2
3
4
5
6
7
8
9
10
11
12
13
14
15
17
19
21
22
23
24
26
30
(22 rows)
例如，第 1 行和第 25 行已被删除。有没有办法从 1 开始以 1 为增量重新启动索引，同时考虑到与其他表的关系，并以安全的方式完成所有这些操作？
答案1
免责声明：原则上我喜欢让 ID 整洁干净的想法。但是，如果成功，重新编号不会带来任何好处，而如果失败，则会破坏数据库。
首先，最好确保自己是唯一连接到数据库的用户无论如何我们都会使用事务，但这将有助于数据库更快地完成工作。
在对表重新编号之前client，您需要更改FOREIGN KEY引用该表的约束：
Referenced by: TABLE "work" CONSTRAINT "work_clientid_fkey" FOREIGN KEY (clientid) REFERENCES client(clientid)
并将CASCADE动作添加到ON UPDATE（参见创建表参考)：
BEGIN;
ALTER TABLE work DROP CONSTRAINT work_clientid_fkey;
ALTER TABLE work ADD CONSTRAINT work_clientid_fkey
FOREIGN KEY (clientid) REFERENCES client ON UPDATE CASCADE;
COMMIT;
work.clientid当列发生变化时，这将自动更新列client.clientid。完成后，我们可以使用SELECT语句生成新的 ID，并使用结果发出语句UPDATE，如下所示在这个答案中解释。你可以这样做：
BEGIN;
CREATE SEQUENCE temp_client_id;
-- The real work begins
UPDATE client
SET clientid = ids.new_id
FROM (SELECT clientid AS old_id, nextval('temp_client_id') AS new_id
FROM client ORDER BY clientid) AS ids
WHERE clientid = ids.old_id;
-- Clean up
DROP SEQUENCE temp_client_id;
COMMIT;
完成所有这些操作后，您可能需要更新用于为表生成 ID 的序列的值client（我们称之为client_id_seq）：
SELECT setval('client_id_seq', MAX(clientid)) FROM client;