locked
regarding INSERT IGNORE RRS feed

  • Question

  • User944339287 posted

    hi guys, i wonder how can i make my sql statement work as expected. (I wanna insert data only if record doens't exists.)

    INSERT IGNORE INTO category_option (ID, CID, PID) VALUES (NULL, '10008', '10003')

    * ID is auto_increment field.. 

    CREATE TABLE  `category_option` (
      `ID` int(10) unsigned NOT NULL auto_increment,
      `CID` char(8) NOT NULL,
      `PID` char(8) NOT NULL,
      PRIMARY KEY  USING BTREE (`ID`,`CID`,`PID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

    Wednesday, April 2, 2014 4:17 AM

Answers

  • User753101303 posted

    I meant PRIMARY KEY  USING BTREE (`ID`) for the primary key part and http://dev.mysql.com/doc/refman/5.0/en/create-index.html for creating a unique index (CREATE UNIQUE INDEX MyIndex ON Category_Option(CID,PID) USING BTREE or whatever the correct syntax for mySQL is...)

    The point is that ID is auto generated so it will always produce a different value preventing to use a (ID,CID,PID) primary key for this check. If instead you have a unique index on just CID,PID  then mySQL will see if the (CID,PID) couple already exists and should ignore the insert if found.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 2, 2014 7:24 AM

All replies

  • User753101303 posted

    Hi,

    What if you create a primary key on ID and a unique index on (CID,PID) ?

    Don't know much about mySQL but from what I see in the doc, my guess is that it doesn't work because ID is auto incremented. As a result each (ID,CID,PID) triplet you are trying to insert IS unique and is inserted.

     

    Wednesday, April 2, 2014 4:48 AM
  • User944339287 posted

    hi,

    thanks for your replied. but what do u means by reate a primary key on ID and a unique index on (CID,PID) 

    pls advise. TQ

    Wednesday, April 2, 2014 6:36 AM
  • User753101303 posted

    I meant PRIMARY KEY  USING BTREE (`ID`) for the primary key part and http://dev.mysql.com/doc/refman/5.0/en/create-index.html for creating a unique index (CREATE UNIQUE INDEX MyIndex ON Category_Option(CID,PID) USING BTREE or whatever the correct syntax for mySQL is...)

    The point is that ID is auto generated so it will always produce a different value preventing to use a (ID,CID,PID) primary key for this check. If instead you have a unique index on just CID,PID  then mySQL will see if the (CID,PID) couple already exists and should ignore the insert if found.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 2, 2014 7:24 AM
  • User944339287 posted

    Hi.. tq.. it's work as expected

    CREATE TABLE `category_option` (
      `ID` int(10) unsigned NOT NULL auto_increment,
      `CID` char(8) NOT NULL,
      `PID` char(8) NOT NULL,
      PRIMARY KEY  USING BTREE (`ID`),
      UNIQUE KEY `CID` (`CID`),
      UNIQUE KEY `PID` (`PID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;



    Thursday, April 3, 2014 11:28 PM
  • User753101303 posted

    Does it ? Here you have a unique index on both CID and PID so if you try to insert a (CID,PID) couple that doesn't exist yet, if the particular value for CID or PID already exists, it will be ignored even if the CID, PID combination itself is not already found in the table. So double check what you really want that is :
    - to prevent the insert if the value for CID is already found or if the value for PID is already found
    - or to prevent insert if this particular CID,PID combination is already found

    Friday, April 4, 2014 5:56 AM
  • User944339287 posted
    Hi,

    What should i do if i want to prevent insert if this particular CID,PID combination is already found?

    Please advise. TQ
    Tuesday, April 8, 2014 5:30 AM
  • User753101303 posted

    Than it would be something such as (untested) :

    CREATE TABLE `category_option` (
      `ID` int(10) unsigned NOT NULL auto_increment,
      `CID` char(8) NOT NULL,
      `PID` char(8) NOT NULL,
      PRIMARY KEY  USING BTREE (`ID`),
      UNIQUE KEY `MyIndex` (`CID`,`PID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;

    That is rather than to create a unique index on CID and another unique index on PID, create a unique index on the (CID,PID) couple.

     

    Tuesday, April 8, 2014 5:42 AM