Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3619
  • Last Modified:

Scope of a constraint name? (ORA-02264: name already used by an existing constraint)

Hello experts,
I would like to know the scope of constraint name.  I've read the manual (https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/clauses002.htm#g1053592) but couldnt find the needed info.

In particular, when I run the following sql:
CREATE TABLE myTable02 ( ItemID1 RAW(4) NOT NULL, #more fields here# , CONSTRAINT ItemID_p_k PRIMARY KEY(ItemID1) );
an error is returned: ORA-02264: name already used by an existing constraint

Note that before running the above statement, another sql has been run successfully (only the table name is different):
CREATE TABLE myTable01 ( ItemID1 RAW(4) NOT NULL, #more fields here# , CONSTRAINT ItemID_p_k PRIMARY KEY(ItemID1) );

So the question is: is the scope of the constraint name (ItemID_p_k) bound by the schema or the table? If the scope of constraint is within a table, then why do I have the error?

Thanks,
Do
0
dttai
Asked:
dttai
  • 3
  • 3
2 Solutions
 
morphmanCommented:
It is bound by the schema.

You must have different constraint names for every constraint in a schema. It is common practice to include the table name in the constraint name.
0
 
dttaiAuthor Commented:
Thanks morphman,
Just a follow-up question: is the length limit on identifier applied to all including table name, index name, constraint name, etc..? Table name limited to 32 chars seems a little too short for me.
Thanks,
Do
0
 
morphmanCommented:
yes, unfortunateley this is true. However it is even worse for you as the limit is 30 characters, not 32.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dttaiAuthor Commented:
Thanks morphman,
You will get all the points I previously posted (250).
Since I've a number of questions related to  scope, I will just continue to post it here. Those who answer these questions will get the points I just increase.
1) when i check all_indexes, they have at least two fields (index_name, table_name). However, if I create indexes on two tables with the same index name, Oracle still complains. I guess index name is also bound by schema. Anyhow, I found this scope is rather inconvenient. Why don't they just go ahead and use the pair (index_name, table_name) to identify an index. That way two tables can be allowed to have the same name for their indexes.

... Please check back for several more questions.
Thanks all,
Do
0
 
dttaiAuthor Commented:
2) Where can I find the length limit on name for all sorts of identities in Oracle.
I know:
table name: 32 chars
constraint name: 30 chars
index name: 32 or 30?
view name: ??
user name: ??
Better yet, if you happen to know which Oracle doc has all of these info, please share with me.

0
 
morphmanCommented:
dttai.

Table names are definateley only 30 characters in length.

In fact all oracle identifiers are limited to 30 characters in length.

See here:-

http://www-rohan.sdsu.edu/doc/oracle/server803/A54647_01/apa.htm

table a-1

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now