У меня есть таблица под названием 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, принимая во внимание связи с другими таблицами и делая все это безопасным образом?
Отказ от ответственности: В принципе, мне нравится идея иметь красивые и чистые идентификаторы. Однако их перенумерация не дает никаких преимуществ в случае успеха, а в случае неудачи приведет к поломке базы данных.
Во-первых, лучше убедиться, что вы только пользователь, подключенный к базе данных. Мы все равно будем использовать транзакции, но это поможет базе данных быстрее выполнять свою работу.
Прежде чем изменить нумерацию client таблицу, вам нужно изменить FOREIGN KEY ограничения, которые ссылаются на таблицу:
Referenced by: TABLE "work" CONSTRAINT "work_clientid_fkey" FOREIGN KEY (clientid) REFERENCES client(clientid)
и добавить CASCADE действие для ON UPDATE (ср. CREATE TABLE ссылка):
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 оператор для генерации новых идентификаторов и использования результата для выдачи 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;
После того, как все это будет сделано, вы можете обновить значение последовательности, которую вы используете для генерации идентификаторов для client стол (назовем это client_id_seq):
SELECT setval('client_id_seq', MAX(clientid)) FROM client;