Generate SQL to Update Primary Key

Generate SQL to update primary key

If you need to change PK you could use DEFFERED CONSTRAINTS:

SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

Data preparation:

CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY, name VARCHAR(10));
INSERT INTO master(master_id, name) VALUES ('foo', 'bar');

CREATE TABLE detail(detail_id INT PRIMARY KEY, master_id VARCHAR(10)
   ,name VARCHAR(10)
   ,CONSTRAINT  fk_det_mas FOREIGN KEY (master_id) REFERENCES master(master_id));

INSERT INTO detail(detail_id, master_id, name) VALUES (1234,'foo','blu');

In normal situtation if you try to change master detail you will end up with error:

update detail set master_id='foo2' where master_id='foo';
-- ERROR:  insert or update on table "detail" violates foreign key 
-- constraint "fk_det_mas"
-- DETAIL:  Key (master_id)=(foo2) is not present in table "master"

update master set master_id='foo2' where master_id='foo';
-- ERROR:  update or delete on table "master" violates foreign key
-- constraint "fk_det_mas" on table "detail"
-- DETAIL:  Key (master_id)=(foo) is still referenced from table "detail".

But if you change FK resolution to deffered, there is no problem:

ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id) 
REFERENCES master(master_id) DEFERRABLE;

BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master set master_id='foo2' where master_id = 'foo';
UPDATE detail set master_id='foo2' where master_id = 'foo';
COMMIT;

DBFiddle Demo

Please note that you could do many things inside transaction, but during COMMIT all referential integrity checks have to hold.

EDIT

If you want to automate this process you could use dynamic SQL and metadata tables. Here Proof of Concept for one FK column:

CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY, name VARCHAR(10));
INSERT INTO master(master_id, name)
VALUES ('foo', 'bar');

CREATE TABLE detail(detail_id INT PRIMARY KEY, master_id VARCHAR(10),
   name VARCHAR(10)
  ,CONSTRAINT  fk_det_mas FOREIGN KEY (master_id) 
   REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail(detail_id, master_id, name) VALUES (1234,'foo','blu');

CREATE TABLE detail_second(detail_id INT PRIMARY KEY, name VARCHAR(10),
   master_id_second_name VARCHAR(10)
  ,CONSTRAINT  fk_det_mas_2 FOREIGN KEY (master_id_second_name) 
   REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail_second(detail_id, master_id_second_name, name) 
VALUES (1234,'foo','blu');

And code:

BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
DO $$
DECLARE
   old_pk TEXT = 'foo';
   new_pk TEXT = 'foo2';
   table_name TEXT = 'master';
BEGIN
-- update childs
EXECUTE (select 
         string_agg(FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'' ;'
            ,c.relname,pa.attname, new_pk,pa.attname, old_pk),CHR(13)) AS sql
         from  pg_constraint pc
         join pg_class c on pc.conrelid = c.oid
         join pg_attribute pa ON pc.conkey[1] = pa.attnum 
          and pa.attrelid = pc.conrelid
         join pg_attribute pa2 ON pc.confkey[1] = pa2.attnum 
          and pa2.attrelid = table_name::regclass
         where pc.contype = 'f');

-- update parent        
EXECUTE ( SELECT FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'';'
         ,c.relname,pa.attname, new_pk,pa.attname, old_pk)
 FROM pg_constraint pc
 join pg_class c on pc.conrelid = c.oid
 join pg_attribute pa ON pc.conkey[1] = pa.attnum 
  and pa.attrelid = pc.conrelid
 WHERE pc.contype IN ('p','u')
   AND conrelid = table_name::regclass
);       
         
END
$$;
COMMIT;

DBFiddle Demo 2

EDIT 2:

I tried it, but it does not work. It would be nice, if the script could show the SQL. This is enough. After looking at the generated SQL I can execute it if psql -f

have you tried it? It did not work for me.

Yes, I have tried it. Just check above live demo links.
I prepare the same demo with more debug info:

• values before
• executed SQL
• values after

Please make sure that FKs are defined as DEFFERED.

DBFiddle 2 with debug info

LAST EDIT

Then I wanted to see the sql instead of executing it. I removed "perform" from your fiddle, but then I get an error. See: http://dbfiddle.uk/?rdbms=postgres_10&fiddle=b9431c8608e54b4c42b5dbd145aa1458

If you only want to get SQL code you could create function:

CREATE FUNCTION generate_update_sql(table_name VARCHAR(100), old_pk VARCHAR(100), new_pk VARCHAR(100))
RETURNS TEXT 
AS 
$$
BEGIN
RETURN 
-- update childs
(SELECT 
         string_agg(FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'' ;',  c.relname,pa.attname, new_pk,pa.attname, old_pk),CHR(13)) AS sql
         FROM  pg_constraint pc
         JOIN pg_class c on pc.conrelid = c.oid
         JOIN pg_attribute pa ON pc.conkey[1] = pa.attnum and pa.attrelid = pc.conrelid
         JOIN pg_attribute pa2 ON pc.confkey[1] = pa2.attnum and pa2.attrelid = table_name::regclass
         WHERE pc.contype = 'f') || CHR(13) ||
-- update parent        
(SELECT FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'';',  c.relname,pa.attname, new_pk,pa.attname, old_pk)
 FROM pg_constraint pc
 JOIN pg_class c on pc.conrelid = c.oid
 JOIN pg_attribute pa ON pc.conkey[1] = pa.attnum and pa.attrelid = pc.conrelid
 WHERE pc.contype IN ('p','u')
   AND conrelid = table_name::regclass)
;       
END
$$ LANGUAGE  plpgsql;

And execution:

SELECT generate_update_sql('master', 'foo', 'foo');

UPDATE detail SET master_id = 'foo' WHERE master_id ='foo' ;
UPDATE detail_second SET master_id_second_name = 'foo' 
 WHERE master_id_second_name ='foo' ; 
UPDATE master SET master_id = 'foo' WHERE master_id ='foo';

DBFiddle Function Demo

Of course there is a place for improvement for example handling identifiers like "table with space in name" and so on.

How to generate sql command to update primary key with other columns

Since you are changing a primary key , as a matter of fact it is not updating , but adding a new product. So

1. Create a new product from existing one, that will have a new product code

2. Update ALL items from all tables that have the previous product code, replacing the previous foreign key, with the new one.

3. After this you can delete the previous product.

If you try to turn off validation and change the code, after this your db will be broken, and you will not be able to use it again, since you will constantly have the integration error.

How to update primary key

You shouldn't really do this but insert in a new record instead and update it that way.

But, if you really need to, you can do the following:

• Disable enforcing FK constraints temporarily (e.g. ALTER TABLE foo WITH NOCHECK CONSTRAINT ALL)
• Then update your PK
• Then update your FKs to match the PK change
• Finally enable back enforcing FK constraints

manually updating primary key

Don't use max()+1 to generate a primary key. It's not safe for concurrent inserts and it doesn't really scale well.

Just create a sequence and use that:

create sequence pizza_id_seq;

Then synchronize it with the current values in the table:

select setval('pizza_id_seq', coalesce(max(id),1))
from pizza;

Then, instead of changing your INSERT statements to use the dreaded max() + 1, just use the sequence:

INSERT INTO pizza
  (id, price)
VALUES
  (nextval('pizza_id_seq'), 1.75)

how to update primary key value in sql?

The direct answer to your question is to use set identity_insert off. The best place to start is with the documentation.

More important, there is a very simple way to avoid these problems in the future: use explicitly declared foreign key relationships. If you had a foreign key constraint:

alter table state
    add constraint fk_state_country foreign key (countryId) references country(countryId);

Then the delete would not have been allowed.

Script to generate primary key in SQL Server

Use SQL Server metadata tables to fetch a list of the tables you want to edit, then create dynamic SQL that adds the new column as an identity and the primary key:

DECLARE @script nvarchar(max)=''    --contains the dynamic TSQL code
DECLARE @table_schema varchar(max); --contains the name of your schema
DECLARE @table_name varchar(max);   --contains the name of your table
DECLARE @key_Cursor as CURSOR;      --cursor that will loop on your tables

--Here you must add a condition in the where clause that filters only 
--the 50 tables you want to edit
SET @key_Cursor = CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_CATALOG='primary_keys' and TABLE_TYPE='BASE TABLE';

OPEN @key_Cursor;
    FETCH NEXT FROM @key_Cursor INTO @table_schema, @table_name;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            --create the script that adds a new identity column called ID
            set @script=' alter table ' + @table_schema + '.' + @table_name + ' add id int identity(1,1); '

            --add the code that create the primary key using the new ID column 
            set @script = @script + ' ALTER TABLE ' + @table_schema + '.' + @table_name + ' ADD  CONSTRAINT [PK_' + @table_name + '_ID] PRIMARY KEY CLUSTERED ([ID] ASC); '

            --execute the script for the current table
            exec sp_executesql   @script  

            --print (@script)

            --fetch data for the next table
            FETCH NEXT FROM @key_Cursor INTO @table_schema, @table_name;
        END
CLOSE @key_Cursor;
DEALLOCATE @key_Cursor;

This is only a draft script just to show you how to loop on tables: it works but it is vulnerable to SQL injection. Consider using a parametrized query.

How can I alter a primary key constraint using SQL syntax?

Yes. The only way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

---

Related Topics