Solved

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

Posted on 2004-10-19
6
3,364 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
Industry Leaders: 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

756 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