If my answers are too basic, please accept my apology. 1: There is no difference: KEY is a synonym for INDEX.
2: In a syntax description, the use of [square] brackets means that the part in brackets is optional. Whether you include it or not doesn't change the semantics. 3: The PRIMARY KEY is a special index that: * Has to be UNIQUE * Can not be NULL * There can only be one PRIMARY KEY on a table (biut multiple columns can be part of the primary key). I don't know why Dreamweaver adds the KEY PRIMARY KEY definition. There are different ways to define the primary key, AFAIK. 4: The query optimizer will determine which index(es) is (are) likely to provide the best results (most efficient query execution) for your query. As a rule of thumb, index columns that are frequently used in joins and/or where clauses. If you use a function on an indexed column in such a clause, do NOT expect the index to be usable - so avoid using functions on columns in those cases. AFAIK, in v.4.x you can specify hints in your query if the optimizer does not select the "best" index for a particular query. The name of an index does not determine whether it gets used or not. There are several factors that contribute to which index(es) are used for each query. I am not familiar with MySQL's query optimizer algorithm. 5: I'm not sure if any other types of tables implemenrt this in a later version, but AFAIK the FOREIGN KEY in MySQL used to be there only for compatibility, but had no real impact on the database. In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints. Theoretically, a FOREIGN KEY is a constraint that is placed on a column/index. It means that the column references a (matching) column (usually the primary key) in another table. The FOREIGN KEY represents the "child" in a "parent/child" relationship between two tables. To visualize this, consider a simple Department/Employee table relationship: Departments (DeptID, DeptName) 1, Sales 2, Accounting 3, Personnel 4, R&D 5, Manufacturing Employees (EmpID, EmpName, DeptID) 1, Johnny, 3 2, Sammy, 2 3, Terry, 1 4, Sandy, 1 5, Jeannie, 4 6, Billy, 5 7, Stevie, 5 ...etc. You would have the Employees.DeptID defined as a FOREIGN KEY referencing Departments.DeptID. This constraint/restriction means that you will not be allowed to insert or update a row in Employees where the Dept column did not contain a value already existing in the Departments table. It also means you will not be allowed to update the DeptID in the Departments table (or delete a row) if there are dependent rows in the related (Employees) table. The different actions that you can specify with this constraint determine how MySQL will react if you "violate" the restriction (constraint). Action CASCADE means that a change to the "master" table will also be applied (CASCADEd) to the "child" table, i.e. related values will be updated or deleted. AFAIK, action RESTRICT means you'll be denied these actions (error generated and statement not performed). I'm not sure what the SET NULL | NO ACTION | SET DEFAULT actions do, but you can guess... The FORIGN KEY statement is there to implement referential integrity in the database through declarative rules. Of course, you can be sloppy and just not declare the relationships, just construct your queries as if the data is OK... But I won't recommend it. 5b: I'm not sure if this is still true, but AFAIK, the CHECK constraint in MySQL used to be there only for compatibility, but had no real impact on the database. The CHECK (expression) is a constraint on what values are legal in a particular column (or potentially combinations of values in a row). A general description of CHECK Constraints can be fund at http://www.datanamic.com/support/ta001.html. I have been unable to verify whether the CHECK constraint is actually implemented with any table types or in any versions of MySQL. HTH, Tore. ----- Original Message ----- From: "Sheryl Canter" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 25, 2003 7:07 PM Subject: index questions > I have some basic questions that are driving me nuts, and I can't find > answers anywhere. I've been banging my head against the wall for hours and > hours. I've searched everywhere on the internet and looked in every book I > own. I can't find the information. Here are my questions. > > In the CREATE TABLE statement, there are options to create keys and indexes, > and this is where I'm confused. I'll try to be as clear as possible in my > questions because I'm really desperate for help. > > 1. What is the difference between a KEY and an INDEX? These are two separate > options: > > KEY [index_name] (index_col_name,...) > INDEX [index_name] (index_col_name,...) > > I think the difference can't be uniqueness because UNIQUE is also an option: > > UNIQUE [INDEX] [index_name] (index_col_name,...) > > 2. In the UNIQUE option above and in the FULLTEXT option, one of the > optional parameters is [INDEX]. What does this mean? How is the option > different if the word "INDEX" is included? > > 3. Dreamweaver generated some MySQL code that looked like this: > > PRIMARY KEY (CustID) > KEY PRIMARY_KEY (CustID) > > What is the point of the second option--the KEY statement? Why create two > keys on the same column? Is the KEY option necessary if you use the PRIMARY > KEY option? > > 4. How do you use an index or key? If you use the column on which the > key/index is based (are these terms interchangeable??), does MySQL use the > index?? Using the above example, do I have to use the names PRIMARY or > PRIMARY_KEY to use the indexed version of CustID? If I do a join using > CustID, is MySQL reading the table sequentially and bypassing the index > file? > > 5. There are two other options in the CREATE TABLE statement that I don't > understand at all: > > [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name, ...) > [reference_definition] > CHECK (expr) > > My tables have foreign keys in them. Does that mean I need this FOREIGN KEY > statement? If so, then how do I use it? What is the CONSTRAINT parameter? > What is "symbol"? What is "reference_definition"? > > And finally, what is "CHECK (expr)"? The mysql.com manual lists these > without any explanation or examples. > > Thank you hugely and VASTLY in advance if you can clear up these issues for > me. > > > --------------------------------------------------------------------- > 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
