Keys vs Indexes

[sde]

When working with databases, what exactly is the difference between a key and index?

It seems like when you set a key, it indexes by that key, but is there something else?

[sde]

well , here is what i found: when you make a field a 'primary key' , that field is 'unique' and indexed by that field. when you index a field, it does not have to be unique, but will index by that field.

[Sir R4T IV]

right now, i'm just starting my dbms course in my college. i could pretty well do the assignment i was given, to index some tabbles on smoe specific columns, and so on...

forgive me for being a newbie, but what i don't understand is what is all this indexing for? what does index really mean? and what are foreign keys? another thing i didn't understand is referencing in tables. what does referencing another table mean? hope you don't get angry at my ignorance!

[sde]

we don't get angry for n00bs here, this is code newbie =)

indexing is sorta like alphabetizing your records by the field you most likely will be searching for. i didn't understand it very well until i started working with db2. i can't use SQL to read a db2 file ( with rpg on an as/400 ), so i have to read it almost like a text file.

for example, i have a contact table keyed by last name. these 3 records would reside in the database in this order:

sharon dough
joe duran
peter smith

if they were indexed by first name, it would be:

joe duran
peter smith
sharon dough

your SQL searches would work no matter what, but if you were searching by last name and a file keyed by last name, your results would return much faster.

[Sir R4T IV]

So it means the entries in the database are already in order?
suppose i was to enter values in this seq:

sharon dough
joe duran
peter smith
dorothy jones

would the query

select * from names

result in

sharon dough
joe duran
peter smith
dorothy jones

? or in

sharon dough
joe duran
dorothy jones
peter smith

[sde]

yes, i'm pretty sure in most databases, it will be in order of the index unless you specify an 'ORDER BY' clause in the query.

[Sir R4T IV]

Hey thanks a lot.