Expand my Community achievements bar.

Changing Primary Key of schema from Custom to Auto PK

Avatar

Level 2

I have a requirement where i want to change the Primary Key from internal to Auto PK.. How do i do it?


If i change the key and update database structure .. i get this error message;

08:00:51 - PGS-220000 PostgreSQL error: ERROR:  could not create unique index "schemaName_id" DETAIL:  Key (ischemaid)=(0) is duplicated.

08:00:51 - WDB-200001 SQL statement 'CREATE UNIQUE INDEX XXXXXXXXXXXXX_id ON XXXXXXXXXXXXXXX could not be executed.

08:00:51 - The update of the physical structure of the database has failed

08:00:51 - XSV-350122 An error occurred and the process has been stopped.

9 Replies

Avatar

Level 2

Please post the name of the program you use so a Moderator may move this message to that forum

-A program would be Photoshop or Dreamweaver or Muse or Premiere Pro or ???

Avatar

Level 2

This appears to be a question about SQL syntax rather than an Adobe  product.  See if this helps you.

How to change PRIMARY KEY of an existing PostgreSQL table? · GitHub

Avatar

Level 2

Campaign is also my guess! And before flooding the fora, inamw21812872 should first answer our questions as this will not lead to qualified answers as such a SQL database access is not necessary with CC products.

But I supposed John moved from Forum comments​ and it shouldn‘t be in Adobe Creative Cloud​ at all.

Avatar

Level 2

Discussion successfully moved from Adobe Creative Cloud to Adobe Campaign Classic

Adobe Campaign Standard​ or Adobe Campaign Classic​ ?

I moved to campaign classic now. A different moderator can move to standard or even still a different forum if that is necessary.

Avatar

Level 4

Hi @inamw21812872,

 

Is your issue resolved? If not, you can try below SQL statements to resolve your issue.

 

1) If your schema is already having the sequence, you can use the same or you can define a sequence using the below command.

  • create sequence <give sequence name>

 

2) If the sequence is already there, then ignore point 1 above. Use the below command and set the next value for your table sequence using the SQL command.

  • alter table <Your schema name> alter <primary key attribute> set default nextval('<your sequence name>');

 

Please let me know if this helps you.

 

Thanks,

Raviteja Gundu.

Avatar

Community Advisor

Hi @inamw21812872 ,

 

Please do the below Steps:

 

As you are facing issue due to duplication of index, firstly drop the index which is already there on Schema.

 

drop index <schemaName_id>

 

Next try removing the existing Primary key where internal attribute is defined.

 

Next try changing the primary key by adding autopk="true" to your schema and follow the below steps:

 

1) create sequence <newsequencename>
2) alter table<Schemaname> alter <autopkcolumn> set default nextval('<sequencevalueshowinPreviewTabOfSchema>');

 

Note: These SQL commands should be executed in "SQL code" Activity in Campaign.