database question

Anon

What is the difference between "index" and "key"? And what is "PRIMARY key", "foreign key"? How they are different?

tomhath

Key is one or more columns what you use to select a row or set of rows, or ensure uniqueness (a unique key means no other row in the table can have the same value, that's usually the Primary Key).

Index is like a special table that contains only the keys from a real table; it is ordered by the key values. Used to give fast access into the real table.

Foreign key is a key in table A that links you to a row in table B (usually a Foreign key in A points to the Primary key in B, but the key doesn't have to be primary in B to exist as a foreign in A).

For example, you might one table with peoples' names and a unique (primary) key ID column; and a second table of addresses that has a foreign key column which is a person's ID in the first table so you can link thee address to a person.

Anon

A key is a non repeating value that lets you refer to one table from another eg there might be 300 John Smiths in Candas but only one of them has this social insurance number 999-999-999 the social insurance number can be used as a primary key since a primary key is absolutely not allowed any duplicates then you can search for John Smith with # 999-999-999 and it will give you the correct record. A table must have a primary key and usually it is an autonumber so it doesn't have to be hand coded. You can call a tables primary key from another table to get information from its record eg you have a province table Alberta has a primary key called provID and you have an address table this address table has a primary key called addressID but it also has a foreign key called provID. It reads the provID from the address table then goes into the province table searches for that provID and then puts the province name in its place. Using keys helps reduce the byte size of the database because it allows values entered only once instead of what could be thousands or more times.
So in short a primary key is an non duplicated identifier of a record and a foreign key is another tables primary key
An index is when you put a table in order such as LastName is indexed to sorted in a A to Z order

Anon

Thanks. Is there any sources/articles for me to learn more about those such as making decision on which field should be index/key?