SAP HANA - cannot drop primary key of referenced table

[Former Member]

Hello,

I am trying to execute the following query from HANA studio(HANA version 1.00.74.00.390550)

ALTER TABLE SCHEMA_NAME.MY_TABLE DROP PRIMARY KEY;

HANA issues error saying

SAP DBTech JDBC: [7] (at 23): feature not supported: cannot drop primary key of referenced table: GENERIC_OBJECT_T: line 1 col 24 (at pos 23)

Is there any permission issue? Is there any problem with the drop constraint query? Is it not possible to drop the constraint? I m trying to drop primary key because i need to add additional column to the primary key.

Thanks,

Suren.

[sreehari_vpillai · Accepted solution]

Need to drop all the dependencies (like foreign keys) and then you alter your primary keys of the base table. Because, as relationship exists between another table(which may contain relative data), dropping base constraints will invalidate the relationships.

Sree

[Former Member · Accepted solution]

Thanks for you reply. Is there any way to disable foreign keys?

Thanks,

Suren.

[henrique_pinto]

Apparently this key is being referenced by other table (maybe a foreign key)?

Do you have any views built on top of it?

[Former Member]

Hi Henrique Pinto,

Thanks for your response. This key is used as foreign key in other table. How can i add another column to existing primary key which is already referenced by other table. I want to add another column on which we are going to range partition our table.

Thanks,

Suren.

[henrique_pinto]

I think you will need to drop your foreign key then drop the primary key, add the new primary key and the new foreign key.

[Former Member]

Thanks for you reply. Is there any way to disable foreign keys?

Thanks,

Suren.

[henrique_pinto]

Last time I worked with foreign keys, it was in SPS5, but I assume it hasn't changed much. Check my blog from then:

Basically, what you need to do is to check the FK constraint name in this internal view: REFERENTIAL_CONSTRAINTS - SAP HANA SQL and System Views Reference - SAP Library

Then use a regular ALTER TABLE <table> DROP CONSTRAINT <constraint>.