我有一個名為 client 的表，它依賴其他表的輸入。具有以下規格：
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
免責聲明： 原則上我喜歡讓 ids 乾淨整潔的想法。然而，如果成功，對它們重新編號沒有任何好處，而如果失敗，則會破壞資料庫。
首先，最好確保自己，你是只有用戶連接到資料庫。無論如何，我們都會使用事務，但這將有助於資料庫更快地完成工作。
在對錶重新編號之前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;