RE: String composite key vs auto_increment
> > Disclaimer: SQL/database newbie here. > > Nothing wrong with being a newbie :-) Thanks for the help. > > 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? [snip useful info] > > BTW, there's nothing to stop you from adding another > unique index besides the primary. See the CREATE TABLE / > ALTER TABLE commands in the manual. This would prevent > you from inserting any duplicates. > > / Carsten > -- > Carsten H. Pedersen > keeper and maintainer of the bitbybit.dk MySQL FAQ > http://www.bitbybit.dk/mysqlfaq This begs the newbie question of what, in a nutshell, happens on an insert? Perhaps too newbie for this forum, but I thought I'd ask. I am ignorant of indexes etc, so I guess I only considered the primary key being checked for a dupe during an insert. Thanks, Chris - 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
RE: String composite key vs auto_increment
> That's not really true. You can use an auto_increment field as the primary > key and create a seperate UNIQUE index that combines both the firstname and > lastname fields that will be inforced for inserts. > Usually its easier to work with integers as primary keys, especially when > you reference them in other tables and such. I assume its slightly faster > for MySQL to work with shorter integers than longer strings as primary keys > but I could be wrong. > > Chris > It's not just slightly faster -- it's WAY faster, especially as your tables grow larger. 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
Re: String composite key vs auto_increment
> > 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
RE: String composite key vs auto_increment
> Disclaimer: SQL/database newbie here. Nothing wrong with being a newbie :-) > 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? > > 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. Your observations are correct -- as long as you're only concerned about that one table. But as soon as you start entering books (which I assume you do in another table), you have to link the two tables somehow. The most obvious way to do this is to carry over the primary key from the author's table, in your case first and last name -- just as you suggest. BUT - and here's why you should use an integer instead. 1) Strings generally take up more space than integers - and as you will probably be storing lots of data in your books table, the strings first and last name will be stored over and over, taking up more space than would an integer. 2) Maybe more importantly: Suppose you discover that an author's name is misspelled -- you'll only have one place to make the update, rather than every table referenced by the first/last name combo. BTW, there's nothing to stop you from adding another unique index besides the primary. See the CREATE TABLE / ALTER TABLE commands in the manual. This would prevent you from inserting any duplicates. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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
Re: String composite key vs auto_increment
Christian Stromberger wrote: > Disclaimer: SQL/database newbie here. > > 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. > 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. - 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
RE: String composite key vs auto_increment
That's not really true. You can use an auto_increment field as the primary key and create a seperate UNIQUE index that combines both the firstname and lastname fields that will be inforced for inserts. Usually its easier to work with integers as primary keys, especially when you reference them in other tables and such. I assume its slightly faster for MySQL to work with shorter integers than longer strings as primary keys but I could be wrong. Chris -Original Message- From: Christian Stromberger [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 08, 2001 5:59 PM To: Mysql Subject: String composite key vs auto_increment Disclaimer: SQL/database newbie here. 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? 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. (Any pointers to good resources besides advice from this list also appreciated) Many thanks. -Chris - 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
