The insert will only be bounced where you specify the columns as unique. Thus you need either separate UNIQUE indexes on Name and Value, if you want them to be individually unique, or a single joint UNIQUE index if you want them to be jointly unique but separately duplicable. The INSERT command only checks columns that is instructed are to be unique.
The purpose of the IGNORE modifier is simply to ignore the error produced when a duplicate occurs. Alec Hal Vaughan <[EMAIL PROTECTED]> 24/08/2005 07:47 Please respond to [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject INSERT IGNORE Doesn't Seem To Work I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = "Variable1", Value = "100"; or INSERT IGNORE INTO myTable (Name, Value) VALUES("Variable1", "100"); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]