Systems Engineering and RDBMS

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.

• Tables
• Views
• Sequences
• Private synonyms
• Stand-alone procedures
• Stand-alone stored functions
• Packages
• 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;

Table created.

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);

Index created.

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.

• Indexes
• Constraints
• Clusters
• 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
• Tablespaces
• Profiles
• 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;

Synonym created.

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.

Resources:

  • Oracle 10g SQL Reference Manual – here.
  • Oracle 11g SQL Fundamental Exam Guide by John Watson and Roopesh Ramklass

3 Responses to “Back to the basics: Can I have same name for table and index?”

  1. kirakast said

    nice doc

  2. sweekar said

    From this page i have learned that table name and index names could be same…

    But what will happen if …

    select * from t1;
    this query is written will it be executed or not?
    if yes what contents will be printed of table or of index;
    if no why is it so??

    regards
    sweekar

    • Sweekar,

      Firstly, as we indicated in the post, even though a table and an index can have the same name, it is never a good practice to do so.

      Secondly, the index (if based off the same table) is essentially going to used for data retrieval if it is optimal to do so. If that index is based off another table, then it is a moot point. You do not select from an index – you select from the table … indexes are used by the optimizer to facilitate a better execution plan for data retrieval.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: