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?
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" are three different identifiers.
Note: Single quotes are for text literals and double quotes are for quoted identifiers.