Back to the basics: Can I have same name for table and index?
Posted by decipherinfosys on April 21, 2009
A question asked by a junior DBA at a client site was: “Can I have the same name for a table and it’s index?”. Now, why would anyone want that to happen? He did not want that either but he had inherited a schema and was surprised to see a table and an index having the same name.
Since the question was related to an environment in Oracle RDBMS, we will cover that in this post. We will cover the SQL Server one at a later stage. Each object in the database is uniquely identified with a schema name and an object name combination. In the database, each schema has its own namespace in which it contains the objects. So, can we have same name for table and view or table and index? Answer is no and yes respectively. If objects belong to the same namespace then they can’t have same name within the schema but if objects belong to different namespace then they can have same name within the schema itself. How do we know that objects are in same namespace or different namespace? This is dependent on the object type. Each namespace contains set of object types (tables, views, procedures etc.) and no object in the same namespace can have same name even though they are having different object type. As per Oracle documentation following object types share the same namespace within the schema so they cannot have same name.
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Materialized views
• User-defined types
Let us test it now. We will first create the table.
SQL> create table t1 as select * from all_objects where 1 = 0;
Now we will try to create sequence and synonym with the same name as table. We should not be able to create it as sequences and private synonym belong to the same namespace as table’s namespace.
SQL> create sequence t1 start with 1;
create sequence t1 start with 1
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create synonym t1 for t1;
create synonym t1 for t1
ERROR at line 1:
ORA-01471: cannot create a synonym with same name as object
Now let us try to create index with the same name.
SQL> create index t1 on t1(object_name);
We were able to create an index with the same name as table name because indexes reside in different namespace. In fact following object types have their own namespace.
• Database triggers
• Private database links
Since constraints also have its own namespace, we can create same constraint with the same name as an index and a table.
Certain other non-schema objects like roles, public synonyms etc. also has its own namespace and since they are not tied to schema, they span across the database. Following are such object types, which span across the database. So they can also contain the same name.
• User roles
• Public synonyms
• Public database links
• Parameter files (PFILEs) and server parameter files (SPFILEs)
If we try to create public synonym, it will be created successfully without any problem.
SQL> create public synonym t1 for t1;
We are just revisiting the basics here to explain why we can’t create a table and a view with the same name but we can create a table and an index with the same name. As a best practice, it is never a good idea to give the same name to different objects. It unnecessarily creates confusion. There should be a nomenclature that should be followed religiously for the schema objects which makes it easy to work in larger teams or even smaller teams. One thing that we have done in all our projects is to use a data modeling tool like ERWin or Sybase’s PowerDesigner and enforce the naming standards in that tool. All the DDLs are then generated through that tool and also checked into source code control.
- Oracle 10g SQL Reference Manual – here.
- Oracle 11g SQL Fundamental Exam Guide by John Watson and Roopesh Ramklass