Re: Oracel-SQL Question

fbap3120_at_leonis.nus.sg (Virinchipuram J. A.) writes:

> Hi Oracle gurus,
> I have just started using oracle, and please forgive me for this
> "probably" simple question:
 

> I am using SQL-plus for windows
> I have created a table-
 

> Create Table Company_Profile (
> Company_code NUMBER(4) NOT NULL,
> Date_of_Joinig DATE,
> Primary Key (Company_code) )
 

> Create Unique Index company_profile_index ON Company_Profile
> (Company_Code)
 

> The above two have succeded.

I presume that you Did have the semi-colons after the SQL commands, and SQL*Plus Did tell you things like "table created" and "index created".

Strictly speaking, you don't actually need to create a (unique) index on your column Company_code, as identifying it explicitly as the Primary Key will create a unique index on that column for you. I think this is even true for Oracle version 6, wherein a lot of data integrity was not implemented.

Being as you don't name the Primary Key constraint, the unique index created for you will have a system-generated name, like SYS_C00345, for example. In order to give the automatically created unique index a name more to your liking, use the syntax

        Create Table Company_Profile (
        Company_code NUMBER(4) NOT NULL,
        Date_of_Joinig DATE,
        Constraint company_profile_index Primary Key (Company_code) );

and don't bother with the subsequent Create Index Statement.

> But when I say-
 

> Drop Index company_profile_index
> it gives me an error saying no such index.

This leads me to believe that the index company_profile_index was not created in the first place, for some reason.

> But I checked the table structure, by attaching this in MS-Access
> and when I check the name of the index, it is
> Username_SYS_c00345

You are seeing, here, the automatically, and correctly, created index, created as a result of your Primary Key clause. If your index company_profile_index was also created, then you should be able to see that one also.

> In other words Oracle has given a different name to the index.

This is not true; see above.

> The index is not named as company_profile_index
 

> Can anyone shed Light on this?

Hope this helps.

> Thanks for the effort.

You're welcome.

> --
> Anand. V. J Internet:fbap3120_at_leonis.nus.sg
> Dept Of Decision Sciences
> National University Of Singapore

Hank Robinson
Oracle DBA
University of Warwick Received on Sun Sep 18 1994 - 20:24:51 CEST