I did read in the manual that the field level REFERENCES constraints on InnoDB tables do not work as expected and one has to first define a table level index and then create a table-level FOREIGN KEY constraint for the field to make it work.
I believe this is just a hack to keep things faster... if you have a FOREIGN KEY constraint on some columns that do not have an index the FOREIGN KEY check will take longer... if you do... then the check will be faster... this was the reason for this ! Starting with MySQL 4.1.2, these indexes are created automatically... you only need to make the FOREIGN KEY constraint.
I just would like to know if that's the case with the field-level PRIMARY KEY constraints as well. Do I have to define table-level PRIMARY KEY constraints as well?
No... not on PK ! (as far as i know)
I also did read that InnoDB is very good at long PRIMARY KEYs. Does "long primary keys" means keys having more than one field? or keys having a greater character length?
Under contrary in many articles about optimizations i've sen "keep those primary keys small on InnoDB..." (yeap found it: http://jeremy.zawodny.com/mysql/mysql-optimization.html - slide:37/80) -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]