> > Let's say I have a table of authors with columns for last name and first > > name. Is there any general guideline re using a separate integer for the > > primary key as opposed to a combination of the last and first names as the > > key? I ask because by using the names, this would prevent duplicate entries > > into the db, right? Whereas using a separate integer key would not prevent > > this--you'd have to search for the author to see if it was already in the db > > before inserting to avoid dupes, right? > > Yes. Yes. On the second point, you would want to make a [non-unique] key on > the first and last name columns. >
I think he actually wants a unique key here, since he wants to prevent duplicate entries. > > > > Assume I am not concerned about there being two different "Joe Smith" > > authors that are different people. I only want to associate an author name > > with a book. > > The column you seek is an integer column with the auto_increment flag, e.g.: > > CREATE TABLE authors ( > last_name char(64) DEFAULT '' NOT NULL, > first_name char(64) DEFAULT '' NOT NULL, > author_idx integer NOT NULL AUTO_INCREMENT, > PRIMARY KEY( author_idx ), > KEY( last_name(20), first_name(20)), > KEY( first_name(20)) > ); > > The key on only first_name is there in case you query on just the first_name. > > The manual will tell you lots more about auto_increment and how keys are used. > > b. If searches will always be on exact matches, then use a hash value for the primary key -- it's an integer, will allow quick lookups on the author name, and will prevent duplicates. In essence, it gives the best of both worlds, with one exception: you can't do partial lookups or <> type searches. Steve Meyers --------------------------------------------------------------------- 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
