Solved

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

Posted on 2004-10-19
6
3,382 Views
Last Modified: 2012-08-13
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
Comment
Question by:dttai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 6

Accepted Solution

by:
morphman earned 500 total points
ID: 12345378
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
 

Author Comment

by:dttai
ID: 12345423
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
 
LVL 6

Expert Comment

by:morphman
ID: 12345439
yes, unfortunateley this is true. However it is even worse for you as the limit is 30 characters, not 32.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dttai
ID: 12351618
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
 

Author Comment

by:dttai
ID: 12351659
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
 
LVL 6

Assisted Solution

by:morphman
morphman earned 500 total points
ID: 12352215
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question