I'll take a stab at this. A key is a value that you can use in a table and relate it to another table. In this usage you will hear people talk of primary keys and foreign keys.
A primary key is a value that uniquely identifies a record in a table. With VERY FEW exceptions, all tables should have a primary key. I prefer a primary key that does not mean anything (i.e. a sequential number using MySQL's auto increment instead of SSN or phone number) The primary reason against these 'smart keys' is that no matter what you choose, you will eventually have one that is wrong and has to be changed. Then you orphan all the child records in other tables until you update them. It's a real PITA. (BTW, a primary key can have multiple parts if necessary...but again KISS is the best rule) A foreign key is a primary key form another table, stored in a record to relate that record back to the 'parent'. This allows you to store related data in separate tables but retrieve it easily. My favorite example is people an phones. 1 person may have n phone numbers. your choices for modeling this are either keep adding columns to your person table each time you come up with a new phone type or add a phone table and store the phone number and the personID as a FK. Then you can select * from phone where personID=4 to get all of person #4's phones. (NEVER use Select *!) An Index is a mechanism to allow for faster searching through the database. Index can be unique, as in the case of primary keys. This forces the value to be unique and will throw an error if you insert a duplicate value into a field that has a unique index on it. General rule of thumb, every table should have exactly 1 primary key and 1..n indexes. Both of these are generalizations but they should help you. Or not... =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -----Original Message----- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 10:45 AM To: [EMAIL PROTECTED] Subject: Simple Question relating to Indexing Hello all being relatively new on the SQL scene I must say some of the concepts are a little new and strange to me. My really big question is what is the difference between an index and a key? are the synonamous with each other? Thanks in advance Shaun Bramley query mysql --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
