How to change the name of constraint or drop it that has dot (.) inside its name?-CodePudding

I have created a constraint named version.pk while back ago on some table. Now, I want to drop it using the following query

alter table user.some_table drop constraint version.pk

However it's giving me an error saying ORA-01735: invalid ALTER TABLE option. I think it's because the constraint has dot (.) inside it and oracle does not recognize the query as a valid one. I wanted to change the name of the constraint to version_pk with the following query

alter table user.some_table rename constraint version.pk to version_pk

But again the same issue this time with different error ORA-00946: missing TO keyword . But I think the reason is the same, as it is not a valid query.

One solution that I have found for primary key type of constraints is this

alter table user.some_table drop primary key

But my constraint is foreign key type. Is there any way to refer constraint name as a literal text, something like 'version.pk', so that query remains valid?

CodePudding user response：

Use a quoted identifier:

alter table user.some_table drop constraint "VERSION.PK";

Note: when you use quoted identifiers, you MUST use the correct case for all characters in the identifier as "VERSION.PK", "version.pk" and "VeRsIoN.pK" are three different identifiers.

Note: Single quotes are for text literals and double quotes are for quoted identifiers.