Change Key
Hi Everyone!!
I have selected wrong Field as "Use as key" in data class , is there any possible way to change or deleting class is the only way. and i am not able to see created data class in Refactor "Deleting Class
Accepted Solution
Updated: 17 Oct 2022 6:34 EDT
Lventur
IN
Please follow below mentioned approach for changing key... Use RDB-Open and RDB-Save method in activity and execute below mentioned query.. In dev environment alone before running the below mentioned query export all record(s) from table and make sure in table you are not having any record(s)
1 - Create an Connect SQL and have the below mentioned query under Open method tab... This query is for getting table constraint key value...
Note - While running query please enable tracer, in tracer from RDB-Open method step page you will be able to get Constraint key value..
SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = 'customerdata' AND rel.relname = 'pr_test_data_outcom';
customerdata = Table Schema Type = Customerdata/PegaData
pr_test_data_outcom - DB Table
Please follow below mentioned approach for changing key... Use RDB-Open and RDB-Save method in activity and execute below mentioned query.. In dev environment alone before running the below mentioned query export all record(s) from table and make sure in table you are not having any record(s)
1 - Create an Connect SQL and have the below mentioned query under Open method tab... This query is for getting table constraint key value...
Note - While running query please enable tracer, in tracer from RDB-Open method step page you will be able to get Constraint key value..
SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = 'customerdata' AND rel.relname = 'pr_test_data_outcom';
customerdata = Table Schema Type = Customerdata/PegaData
pr_test_data_outcom - DB Table
2 - Create another Connect SQL (or) use the same one which you have created earlier and under Save method tab place the below mentioned query. this table is to drop table existing constraint...
ALTER TABLE customerdata.pr_test_data_outcom DROP CONSTRAINT pr_test_data_outcom_data_outcom_pk;
pr_test_data_outcom_data_outcom_pk = Table Constraint Key Value
3 - Create another Connect SQL (or) use the same one which you have created earlier and under Save method tab place the below mentioned query. This query is to add/change new key...
ALTER TABLE customerdata.pr_test_data_outcom ADD PRIMARY KEY(test1,test2);
customerdata.pr_test_data_outcom = DB Schema.Table Name
test1,test2 = DB Column Name, which you are trying to have as a key for table.
After this 3 query got executed open the class record rule and update the key... If you need to deploy the changes to higher environment include the DB Schema and Class key in product and deploy. After you deployed the schema and class rule automatically in higher environment table key will get updated and before deploying this query no need to delete any record from table in higher environment...
SAMI AEC
SA
You can follow the below steps to change the key.
- If the data type which you have created hold the records . Export all the records to excel and empty the table.(Data type)
- Once records are deleted from the data type post taking back up (Exporting to excel sheet) . Open the class record for that data type from Data type
- In the class record, under General tab , under Keys , you can delete the existing key and add the Key which you would like to have as key for the data type.
Please note that Key option in the class record will be enabled only if the data type should not hold any records.
- Save the class record and go the Data type to check if the newly mentioned key reflect as a key for the data type.
Accepted Solution
Updated: 17 Oct 2022 6:34 EDT
Lventur
IN
Please follow below mentioned approach for changing key... Use RDB-Open and RDB-Save method in activity and execute below mentioned query.. In dev environment alone before running the below mentioned query export all record(s) from table and make sure in table you are not having any record(s)
1 - Create an Connect SQL and have the below mentioned query under Open method tab... This query is for getting table constraint key value...
Note - While running query please enable tracer, in tracer from RDB-Open method step page you will be able to get Constraint key value..
SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = 'customerdata' AND rel.relname = 'pr_test_data_outcom';
customerdata = Table Schema Type = Customerdata/PegaData
pr_test_data_outcom - DB Table
Please follow below mentioned approach for changing key... Use RDB-Open and RDB-Save method in activity and execute below mentioned query.. In dev environment alone before running the below mentioned query export all record(s) from table and make sure in table you are not having any record(s)
1 - Create an Connect SQL and have the below mentioned query under Open method tab... This query is for getting table constraint key value...
Note - While running query please enable tracer, in tracer from RDB-Open method step page you will be able to get Constraint key value..
SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = 'customerdata' AND rel.relname = 'pr_test_data_outcom';
customerdata = Table Schema Type = Customerdata/PegaData
pr_test_data_outcom - DB Table
2 - Create another Connect SQL (or) use the same one which you have created earlier and under Save method tab place the below mentioned query. this table is to drop table existing constraint...
ALTER TABLE customerdata.pr_test_data_outcom DROP CONSTRAINT pr_test_data_outcom_data_outcom_pk;
pr_test_data_outcom_data_outcom_pk = Table Constraint Key Value
3 - Create another Connect SQL (or) use the same one which you have created earlier and under Save method tab place the below mentioned query. This query is to add/change new key...
ALTER TABLE customerdata.pr_test_data_outcom ADD PRIMARY KEY(test1,test2);
customerdata.pr_test_data_outcom = DB Schema.Table Name
test1,test2 = DB Column Name, which you are trying to have as a key for table.
After this 3 query got executed open the class record rule and update the key... If you need to deploy the changes to higher environment include the DB Schema and Class key in product and deploy. After you deployed the schema and class rule automatically in higher environment table key will get updated and before deploying this query no need to delete any record from table in higher environment...
Morgan Stanley
IN
i dont think ALTER TABLE query works under SAVE tab. when i tried in 8.7.4 i was getting DB exception saying ALTER DDL should not use save method. later i kept the query in DELETE tab it worked.
EverestRe
US
@Gunasekaran Baskaran : We tried this and it worked in Dev environment , however when we moved this to higher environment the updated key did not move ,please advise