Postgresql find foreign key references and reset primary key serial without truncate table

in this blog post i will try to explain how to find Postgresql foreign key references and reset primary key serial.

now question is why we need to find Postgresql foreign key references and reset primary key serial ? 

sometime we have a scenerio where we need to reset the primary key for business need at the same 

time we need to keep data as is without truncate the whole table. lets go into the details

i have a table name as categories, image shown above. problem is id 1 is deleted somehow.

now category Pharmacy has id 2, but as per business need, Pharmacy has to be category id 1.

so now i need to move Pharmacy category to id 1, but Postgresql wont allow me to do normally, 

cause id 1 already used.to overcome this issue we will do the below steps. 

i'm using two database client to accomplish this for ease of work.

one is DBeaver and pgAdmin 4.

first backup whole database,in case of any error, we can restore the database.

now run the below sql query with where kcu.column_name = 'your_primary_key', 

for my case the primary key name is category_id

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema

where kcu.column_name = 'category_id'

check the below image

now check the table_name column, you will find all the foreign key references

related with category_id, ignore top 1 result, because this from same categories tables.

now we will remove the foreign key reference from 3 tables

deals

event_categories

product_categories

lets start with deals, see the images below

now event_categories table, we will remove category_id foreign key reference, see the images below

now product_categories, see the images below

now open the categories table using pgAdmin4, click on Properties

click Column tab, click on edit icon left of primary key(category_id) column.

now click Constaints tab, now check type, select NONE from IDENTITY

click save.

see image below 

now open the categories table data, and modify primary key id

see image below

again open the categories table using pgAdmin4, click on Properties

click Column tab, click on edit icon left of primary key(category_id) column.

now click Constaints tab, now check type, select IDENTITY from NONE.

select always for identity click save. see image below

now in query editor run the following query

SELECT pg_get_serial_sequence('"public"."categories"', 'category_id'); // output: "categories_category_id_seq"

this will output the "categories_category_id_seq" see image below

now run the following query 

SELECT nextval('"public"."categories_category_id_seq"'::regclass); // output 1

this will output next primary key serial number. see image below

now run the following query to reset the primary key serial

SELECT setval('"public"."categories_category_id_seq"', 67); 

see image below.

now run the following query 

SELECT nextval('"public"."categories_category_id_seq"'::regclass); // output 68

this will output next primary key serial number. see image below

now restore the category_id foreign key reference for 

deals, see the images blow

now restore the category_id foreign key reference for event_categories, see the images blow

now restore the category_id foreign key reference for product_categories, see the images blow

we are done!

Tags : MySQL, PostgreSQL,