How can I disable a unique constraint whose name is lowercase?-CodePudding

So I have the following constraint (queried from user_constraints):

However, whenever I run

ALTER TABLE fnn_fin_inst DISABLE CONSTRAINT short_code_unique;

I get the following error:

There are similar constraints that I can disable and enable with no problems. The only difference I can see with this one is that its name is in lowercase. However, Oracle seems to automatically convert the given constraint name to uppercase so I can't get it to attempt to disable the correct constraint. I also can't change the constraint's name for the same reason.

CodePudding user response：

Oracle SQL is generally case-insensitive. Identifiers (names of tables, columns, indexes, views, etc. - including constraints) are saved in the catalog tables in upper-case. When we use identifiers in our queries, they are first converted to upper-case.

UNLESS

we use double-quotes. First when we create the names: if we want them to be case sensitive, we use double-quotes in the create ... statement, like so:

create table ...
( ... columns ...
  constraint "my_constraint" check (... something ...)
);

Notice the constraint name in double-quotes.

Then whenever we reference that name, it must appear in double-quotes in our statements.

Not like this:

  ... disable constraint my_constraint

but like this:

  ... disable constraint "my_constraint"