asked on

Table creation do i need to add indexes for primary and unique key in oracle

Hi Experts,

I have a below table creation statement.

   CREATE TABLE INF_DDA
   (	"TXNKEY" NUMBER(14,0) NOT NULL ENABLE,
		"GROUPID" VARCHAR2(8 BYTE), 
		"ACCTIDENTIFIER" NUMBER(18,0) NOT NULL ENABLE, 
		"REFNUMBER" VARCHAR2(50 BYTE), 
		"MODIFIEDDATE" DATE,
		"COMPLETEDDATE" DATE,
		CONSTRAINT "PK_INF_TRANXIDKEY" PRIMARY KEY ("TXNKEY"), 
		CONSTRAINT "UNQ_INF_DDA" UNIQUE ("TXNKEY", "GROUPID", "ACCTIDENTIFIER", "REFNUMBER")
   );

Open in new window

NOW I WANT TO ADD INDEXES FOR THE PrimaryKey and Unique Keys
Do i need to add indexes or oracle will do implicitly for both p.key and unique keys?

Why because after i created table i could see the indexes tab(oracle sql developer) these indexes are added.
Now i have confusion like
primarykey by default index will be added
how unique keys also indexes are added?

THANKS,

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sanjeev Labh

I think it is very logical. For not all types of constraints you need indexes. However the two special types i.e. Primary Key and Unique key there will be an index created automatically. While creating these constraints even if you do not mention any index name still database will create the corresponding automatically with the creation of constraint.

Oracle uses these indexes only to enforce uniqueness of the data inserted in these colomuns that is why the indexes for these two types of constraints is mandatory.

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnsone

I disagree with markgeer's point #2.

You can put the index in a separate tablespace without having to create the index separately, or having to specify a full create statement in the alter or create table statements. It can simply be done this way:

CREATE TABLE mytab 
  ( 
     x NUMBER, 
     PRIMARY KEY (x) USING INDEX TABLESPACE users1 
  ) 
TABLESPACE users;

Open in new window

That statement will create the table in the USERS tablespace and the index (with the system named constraint name) in the USERS1 tablespace.

You can specify the tablespace similarly in an ALTER TABLE statement to create the constraint.

Mark Geerlings

Sorry johnsone. I wasn't trying to disagree with your suggestion. I was trying to emphasize that I prefer to create these indexes explicitly, as opposed to letting the database do it implicitly, as in the original question here. You are certainly correct that a separate tablespace for an index can be specified in a "create table ..." command, as long as a command like "using index tablespace ..." is included in the "constraint..." clause.

johnsone

I know you were trying to point out the way you do it, and I agree that is the way that it should be done. Especially given the sample in the original post as one index can be created to enforce both constraints.

I'm just trying to point out that putting the indexes in a different tablespace can actually be done at creation time within the create table statement and putting the table and index in different tablespaces can be done in the create without having to generate the full create statements.

Maybe I just misunderstood how you were stating it.

srikotesh

ASKER

thanks