my insert statement seems to be ignoring my unique index and adding duplicates
what'd i mess up?

thanks in advance


CREATE TABLE `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `address_street1` varchar(100) NOT NULL,
  `address_street2` varchar(100) DEFAULT NULL,
  `address_street3` varchar(100) DEFAULT NULL,
  `address_city` varchar(45) NOT NULL,
  `address_state` char(2) DEFAULT NULL,
  `address_zip` varchar(10) NOT NULL,
  `address_country` varchar(100) NOT NULL,
  `address_latitude` double DEFAULT NULL,
  `address_longitude` double DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  UNIQUE KEY `a_dupe` (`address_street1`,`address_street2`,`address_street3`,`address_city`,`address_state`,`address_zip`,`address_country`)

insert query

set @address_street1 = '1 street name';
set @address_street2 = null;
set @address_street3 = null;
set @address_city = 'some city';
set @address_state = 'SS';
set @address_zip = '00000';
set @address_country = 'US';
INSERT INTO address (address_street1, address_street2, address_street3, address_city, address_state, address_zip, address_country)
VALUES (@address_street1, @address_street2, @address_street3, @address_city, @address_state, @address_zip, @address_country)
ON DUPLICATE KEY UPDATE address_id = LAST_INSERT_ID(address_id);

Recommended Answers

It happens because you're using NULL values, for the series it's not a bug, it's a feature:

Jump to Post

All 3 Replies

cereal is right is happening due to null values which are being assigned.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.