At 13:10 -0500 2/4/02, Erik Price wrote:
>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
Use INSERT IGNORE, or REPLACE.
