I want to thank everyone for their contributions on this thread,and especially Erik for posting the question. I'm in the middle of the problem, and was about to write excessive code using Erik's suggestion No.1. Thank you, thank you to all.
----- Original Message ----- From: "Erik Price" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, February 04, 2002 1:10 PM Subject: INSERTing duplicate values to a UNIQUE-indexed table > Hello, everyone. > > I have a slight dilemma, and was wondering what the standard workaround > is. I have three tables: owners (auto_increment primary key is > "owners_id"), objects (auto_increment primary key is "objects_id"), and > owners_objects (which is a foreign key table that I created, under > advice from someone on this list a while back whose email address has > changed -- there are two columns in owners_objects: "owners_id" and > "objects_id", and there are two unique indexes on the table, > "owners_id / objects_id" and "objects_id / owners_id" -- this is to keep > duplicates combinations in this table, since they would only take up > extra disk space). > > I am designing an application in PHP which stores the relationship > between an Owner and an Object using the owners_objects table in a > many-to-many relationship. When someone adds a new owner, they can > choose from an HTML listbox any number of objects to associate with that > owner. The PHP code creates an INSERT statement that inserts the data > into "owners", and then takes the auto_incremented primary key of the > last insert (which is the insert into "owners") and uses that as the > value for the second INSERT statemetn: to insert into > "owners_objects.owner_id". In this second INSERT statement, the > "objects_id" of the Object(s) selected from the listbox go into the > second column of "owners_objects". > > I am sure that many people have done this sort of setup. But what do > you do to get around the problem of INSERTing a pair of values that > already exist? Because the combinations in "owners_objects" are UNIQUE > (the UNIQUE indexes), MySQL won't accept a pair that is already > present. I see two possible options: > > 1) Check to see if the combination is already present, and if so, do not > run the INSERT query > 2) run the INSERT query regardless and suppress the error message > > The disadvantage of the first one is that it adds an extra SQL query to > the process. The disadvantage of the second one is that I think it is > somewhat tasteless to execute code that will knowingly error -- or > should I just stop trying to be such a perfectionist? > > I would post code but this is all pseudocode right now b/c I haven't > solved this dilemma yet -- all experimentation with this has been done > from the mysql client. > > Thanks for your advice! > > > Erik > > > --------------------------------------------------------------------- > 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
