List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 4 2002 6:30pm
Subject:Re: INSERTing duplicate values to a UNIQUE-indexed table
View as plain text  
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.
Thread
mysqld crash & stack trace from 3.23.47-max (with InnoDB)Jeremy Zawodny4 Feb
  • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Sinisa Milivojevic4 Feb
    • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Jeremy Zawodny4 Feb
      • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Sinisa Milivojevic4 Feb
        • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Jeremy Zawodny4 Feb
          • INSERTing duplicate values to a UNIQUE-indexed tableErik Price4 Feb
            • Re: INSERTing duplicate values to a UNIQUE-indexed tablePaul DuBois4 Feb
            • Re: INSERTing duplicate values to a UNIQUE-indexed tableMarcus Collins4 Feb
              • Re: INSERTing duplicate values to a UNIQUE-indexed tableErik Price4 Feb
              • renumbering auto-increment?savaidis4 Feb
                • Re: renumbering auto-increment?Nathan4 Feb
                • Re: renumbering auto-increment?BD4 Feb
            • Re: INSERTing duplicate values to a UNIQUE-indexed tableDL Neil4 Feb
              • Re: INSERTing duplicate values to a UNIQUE-indexed tableErik Price4 Feb
              • Re: INSERTing duplicate values to a UNIQUE-indexed tableSommai Fongnamthip5 Feb
            • Re: INSERTing duplicate values to a UNIQUE-indexed tableKalok Lo8 Feb
  • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)jocelyn fournier4 Feb
    • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Sinisa Milivojevic4 Feb
  • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)jocelyn fournier4 Feb
Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Heikki Tuuri4 Feb
Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Sasha Pachev4 Feb
  • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Jeremy Zawodny4 Feb
Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Sasha Pachev4 Feb
  • Re: mysqld crash & stack trace from 3.23.47-max (with InnoDB)Jeremy Zawodny4 Feb
Re: INSERTing duplicate values to a UNIQUE-indexed tableKalok Lo12 Feb