Primary Key with a Unique Index? 1

[Petemush]

Hellooo,

I was just wondering on the benefits of having a Primary Key as well as a unique index in a table.

I know that when you create a Primary Key, a Unique (Clustered <- which I'm not sure what this is) Index is automatically created so the Key can be enforced. According to this thread:

thread183-372027

the clustered index is only created so long as a unique clustered index doesn't already exist for that table with the primary key columns. Since that was an SQL Server forum does this hold true for Oracle?

The thread also says not to have more than one index because it slows down insert/update and takes up more space.

So is it possible to create my own unique index and then set the primary key up, assuming that it won't bother to create an index because my one already exists? Meaning that there is only one index so no disadvantage.

And if this is possible, is it a good thing to do?

Cheers,

Pete

 

[sem]

If your own index contains PK fields, you Oracle may use it for PK validation. Having more then 1 indexes may slow-down inserts, but speed-up selects, because index size may also matter. So if you query by PK, using an index including other columns may produce some overhead.


Regards, Dima

http://seminihin.narod.ru

 

[Petemush]

If I'm following correctly then I think I'm alright.

What I'm currently doing is:

1. Create a table with four columns, all not null and name the tablespace I want to use.

2. Create a unique index featuring two of those columns and name the tablespace for the index.

3. Add a primary key to the table featuring those same two columns that make up the unique index.


So now I have a table with a composite primary key and a unique index made of the composite key and that's it.

The act of adding a primary key shouldn't have automatically created another index because an index already existed for that composite primary key. So there shouldn't be any extra overhead.

Is that correct?

Cheers,

Pete

 

[avjoshi]

Yes. And this is a better approach because you can give more meaningful name to the index (system generated index names for the primary key constraint are not very intuitive).
Another advantage of this approach is you have better control over storage parameters.

Anand.

 

[Petemush]

Good to hear I'm on the right track! Thanks for that.

Should the index I create be clustered or is that a question that I have to work out the answer to by thinking about how the data in the table is going to be accessed?

Thanks,

Pete

 

[SantaMufasa]

Anand and Pete,

Actually, you have the same, detailed control over &quot;more meaningful (index) name&quot; and &quot;better control over storage parameters&quot; when you create your PRIMARY KEY right along with your initial table creation. Following is an example of Pete's table create with simultaneous PRIMARY KEY definition, while yielding the benefits that Anand suggested (without separate definition):

CREATE TABLE PETES_TABLE
	(col1	number
	,col2	number
	,col3	number
	,col4	number
	,constraint PETES_PK primary key (col1, col2)
		using index PCTFREE 20
		TABLESPACE data1 -- petes_index_ts
		storage (initial 16k next 16k pctincrease 100 maxextents unlimited)
	)
	PCTFREE 25
	TABLESPACE data1 -- petes_table_ts
	storage (initial 32k next 64k pctincrease 50 maxextents unlimited)
/

Table created.

@stru
Enter table name: petes_table

Col             Column     Data Type [Constraint Type: Name: En-/Dis-abled]
 #                Name    and Length and Enforcement
--- ------------------ ------------- --------------------------------------
  1 COL1               NUMBER(,)     [PK:PETES_PK:ENABLED] NOT NULL/UNIQUE
  2 COL2               NUMBER(,)     [PK:PETES_PK:ENABLED] NOT NULL/UNIQUE
  3 COL3               NUMBER(,)
  4 COL4               NUMBER(,)
Another table (Y/N) ? n

Notice that the code above exercises full control over the constraint/index_name (Pete's PK index is named &quot;PETES_PK&quot, and we exercised total separate control over Pete's INDEX-definition parameters (tablespace and storage) from Pete's TABLE-definition parameters.

So, you achieve the best of all worlds using the above syntax. Let us know if you agree.

Mufasa
(aka Dave of Sandy, Utah, USA @ 14:54 (04Dec03) GMT, 07:54 (04Dec03) Mountain Time)

 

[Petemush]

So the PK and the Index are called the same thing?

I don't know if it's good practice or not but I tend to call my PKS: PK_THETABLENAME i.e. pk_dept for a table called dept, and I don't choose the name for the index, it is defined in the physical design that I create the code from.

Can you do that with your syntax? Also since the index is being created automatically by the creation of a PK would it be unique, i.e. would it be the equivalent index to one created using the CREATE UNIQUE INDEX syntax?

And finally, I seem to remember that indexes created automatically by the creation of pks are clustered. Is that right? I'm still trying to figure out whether I want this composite index as a clustered one.

Thanks,

Pete

 

[SantaMufasa]

Petemush,

Q. So the PK and the Index are called the same thing?
A. Yes.

Q. (Is that a) good practice or not?
A. An excellent practice. Once you know the PK-constraint name, you also know the index name, and vice versa.

Q. Can you do that with your syntax?
A. Not sure what &quot;that&quot; is.

Q. ...since the index is being created automatically by the creation of a PK would it be unique?
A. Absolutely. When you create a Primary Key, by absolute definition, a UNIQUE INDEX must exist (be created) to enforce uniqueness.

Q. ...would it be the equivalent index to one created using the CREATE UNIQUE INDEX syntax?
A. Absolutely.

Q. (Are) indexes created automatically by the creation of pks clustered?
A. Not automatically. In Oracle, you must explicitly create clusters for an index to be clustered.

BTW, Oracle will not let you drop a UNIQUE INDEX that enforces a PRIMARY KEY constraint without first removing the PRIMARY KEY constraint.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:36 (04Dec03) GMT, 08:36 (04Dec03) Mountain Time)

 

[carp]

And for MY .02 USD, it's a good practice to name ALL of your constraints - otherwise, Oracle will name them for you and you're not likely to appreciate the results!
By adopting a naming convention and sticking to it, not only will you be able to look at the constraint name and tell where it resides and what type of constraint it is, but you will also not have to look them up so often. For instance, if you want to disable a unique constraint on your dept table's lname column, you know that the constraint name should be called something like dept_lname_uq. No need to look it up!

 

[Petemush]

Thanks for all the answers to my questions!

I was about to explain that the index name I've been given doesn't have the letters PK in it so I didn't want the PK to be named the same as the index, but I just took another look and it turns out it does have the letters PK in it! So everybody's happy!

Also thanks for the explanation about clusters, I was beginning to suspect you couldn't explicitly create clustered indexes because all the information I had was about SQL Server, not Oracle. So I guess that means the

CREATE UNIQUE CLUSTERED INDEX

syntax doesn't exist in Oracle. Do you just make the two columns clustered then or is it a case of creating a cluster which contains the two columns?

Thanks for all your help and have a star!

Cheers,

Pete

PS The 'that' that I was referring to was could the index, automatically created by the PK creation, be given a seperate name to the PK using your syntax. I guess the answer is no and the point's a bit moot since it looks like I'll be naming the index and the PK the same for good practice!

 

[Cagliostro]

if PETES_TABLE is already created you can add key like

alter PETES_TABLE
add constarint
PETES_PK primary key (col1, col2)


Ion Filipski

 

[GeertVerhoeven]

Hi guys,

I have a small question which relates to this topic. I have a table with 2 columns which and the primary key is composed of these 2 columns.

The speed of inserts/updates is not important but the speed of select statements is.

Most of my joins will look like this:

...
othertable1.col1 = questiontable.col1
and othertable2.col2 = questiontable.col2
...

When I create the primary key, he automatically creates a unique index on the PK (2 columns). Is it a good thing to create another index for the columns separately.

Thanks in advance,

Geert

 

[SantaMufasa]

Geert,

Your PK index produces the equivalent of a separate index on the initial column of the primary key. If you execute queries with just PK column-2 in the WHERE clause, then you may find it useful to produce a single-column index on PK column-2 (but it is unnecessary/not beneficial to create a single-column index on PK column-1).

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.