my insert statement seems to be ignoring my unique index and adding duplicates
what'd i mess up?
thanks in advance
Table
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`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;
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);
