Solved

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

Posted on 2004-10-19
6
3,280 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now