When is ordering not optional in index creation?

AI Thread Summary
The discussion clarifies that creating a unique index is not possible if the table contains duplicate entries for the specified key, such as names or dates of birth. It emphasizes the importance of selecting unique identifiers, like SSN or EmployeeID, for unique index creation. The conversation also addresses the optional nature of ordering in index creation, noting that while ordering can be specified as ascending or descending, it defaults to ascending. Additionally, it highlights that while unique indexes enforce constraints, non-unique indexes can accommodate duplicate entries. Overall, understanding the relationship between data uniqueness and index creation is crucial for effective database management.
momentum
Messages
111
Reaction score
0
red marker part in my book is very confusing.
It says you will not succeed to create index by that.

Is it trying to say that if we have two tables and there is a FK relation in them , then I won't be able to create index in either table?

Can you please help with an example exactly when I won't be able to create index as per that red marker.

dCIE4w3.jpg
 

Attachments

  • dCIE4w3.jpg
    dCIE4w3.jpg
    47.8 KB · Views: 428
Last edited:
Technology news on Phys.org
If you have data in your table (relations) that has duplicate entries and you try to create a unique index using that key, you should not be able to.

For example you can have people with same names, dates of birth, address, department id, title, etc.

Example where it will fail: https://stackoverflow.com/questions...xisting-table-in-mysql-which-contains-records

You should choose something unique like SSN# or EmployeeID for unique index creation, i.e

likely to be a unique table:
EmployeeID FirstName LastName Title Department Salary

vs

unlikely to be unique:
FirstName Gender Department
 
.some confusions ...

cronxeh said:
If you have data in your table (relations) that has duplicate entries ..

1. Are we talking about single table here ? Yes/No?

2 .You wrote table(relations) ? are they same here ?

You should choose something unique like SSN# or EmployeeID for unique index creation
3. You said "unique index creation"...ok ..fine ... can we create non unique index also ?

4. An example,suppose, vehicle_state_code and registration_no combination will make unique tuples in my table.
Shall I be able to create unique index with these two fields? Is it okay?
 
Last edited:
The indexes are per specific table.

The “[unique]” in textbook means it’s optional and just adds more constraints. Whenever you see brackets that means it’s an optional specifier.

You can create index without unique constraints and have duplicate entries like second example below in that book where same name and same salary can exist in the table, for example interns with names like Dave/John/Andrew getting paid zero or minimum wage.

For vehicle state code and registration number question, it depends on the data, you can have same registration number but different vehicle state code, and same registration number if it’s been reused by that state. So given how dysfunctional governments are, you can bet there will be duplicates.
 
cronxeh said:
.

You can create index without unique constraints and have duplicate entries like second example below in that book where same name and same salary can exist in the table, for example interns with names like Dave/John/Andrew getting paid zero or minimum wage.

okay.

Please look at this script.

create index empindex
on EMPLOYEE(Name Asc,Pay_Rate Desc)

Its very confusing that there is Asc,Desc. What index does with this ordering? does ordering is optional ?
 
momentum said:
okay.

Please look at this script.

create index empindex
on EMPLOYEE(Name Asc,Pay_Rate Desc)

Its very confusing that there is Asc,Desc. What index does with this ordering? does ordering is optional ?

Your example is an index (name asc, pay_rate desc). This index will be used when it can be, but it's quite restrictive. For example, these will be quicker:

1. ORDER BY name
2. ORDER BY name, pay_rate desc
3. ORDER BY name desc, pay_rate

And these will be just as slow as before:

1. ORDER BY pay_rate desc
2. ORDER BY pay_rate desc, name
3. ORDER BY name, pay_rate

Ordering is not optional, it just defaults to ascending.
 
Dear Peeps I have posted a few questions about programing on this sectio of the PF forum. I want to ask you veterans how you folks learn program in assembly and about computer architecture for the x86 family. In addition to finish learning C, I am also reading the book From bits to Gates to C and Beyond. In the book, it uses the mini LC3 assembly language. I also have books on assembly programming and computer architecture. The few famous ones i have are Computer Organization and...
I had a Microsoft Technical interview this past Friday, the question I was asked was this : How do you find the middle value for a dataset that is too big to fit in RAM? I was not able to figure this out during the interview, but I have been look in this all weekend and I read something online that said it can be done at O(N) using something called the counting sort histogram algorithm ( I did not learn that in my advanced data structures and algorithms class). I have watched some youtube...
Back
Top