Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-10-19
6
Medium Priority
?
3,448 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 2000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

722 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