The Gateway to Computer Science Excellence
0 votes
Is it possible in general to have two primary indices on the same relation for different search keys ? Explain your answer.
in Databases by Boss (42.4k points) | 86 views

2 Answers

0 votes
No it's not possible to have more than one primary index on same relation. In primary index the search key is the primary key/Alternate key and it is physically ordered in DB, so other keys will automatically be  unordered hence at most 1 primary index is possible for a relation.
by Junior (785 points)
0 votes
If two or more attributes are ordered then yes. Example: Roll Number(unique within institute) and enrollment number(unique within Mumbai University) of relation for an institute.
by Active (3k points)
Two primary key in a table?? How will it be possible?? It is not possible I think.
But mam, index need not be made on the primary key. Also, a non-primary key can also be ordered.

they told

have two primary indices

that means two primary key in the table itself. right?? 


The search key for a primary index need not be the primary key.  

Source- 14.7, Database System Concepts, 7th ed by Korth and Sudarshan

So this way two primary indices can be created.


I am not sure, though

Roll Number(unique within institute) and enrollment number(unique within Mumbai University) of relation for an institute.

We have read, that a table cannot contain more than one primary or unique key. And in korth they are telling about files, not relation. right?? 

I think by file they mean the relation since the relation is stored in a file on a disk. Also on line 7 they have mentioned that in primary indices the search key need not be primary key.

 Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation

@sakharam check these lines.

mostly two primary indices not possible.

How do u find "different search keys" for a single relation? 

Yes, the statement says "generally" the primary key, but we can also create on a candidate key. And we can have two search keys(In the book by Navathe there's a topic on having multiple indices on the same relation)

Mam, I thought since there can be multiple indices and since there is a possibility(though rare) that two candidate keys(used as search keys) are ordered in the same way there can be two primary indices on the same relation.


Suppose we do primary indices in a student table is Roll number and DOB, where only roll number is primary key.

Now, if we search with key field by DOB , will it give correct answer?? because there is a chance that two students can have same DOB some time.

Mam, It will be useful if my search query is select * from Student where DOB= '2000-1-1'  

Here the DOB index will be used. And since we want multiple rows this index works.

But yes, DOB can not be a primary index, only Roll no. will be.
Atleast DOB cannot be a search key.


It can be a search key. The same way in the following example Income was used as a search key for creating index. 

Source: Korth



Key cannot take duplicate values


Otherwise tell me which key can take duplicate values??

Mam, search key need not be a key(primary key/candidate key), right?

Yes mam, I agree about the order thing that generally the order would be different. But I talked about a rare case when order is same.
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,258 answers
104,737 users