MySQL UNIQUE key not working

Question

I am building as type on inventory table that keeps track of stock by 6 different factors. I am using an I query much like this one:

INSERT INTO inventory ( productid, factor1, factor2, factor3, factor4, factor5, factor6, quantity, serial_number)
VALUES (242332,1,1,1,'V67',3.30,'NEW',10,NULL)
ON DUPLICATE KEY UPDATE `quantity` = VALUES(`quantity`) + quantity;

The inventory table has a UNIQUE KEY for ( productid, factor1, factor2, factor3, factor4, factor5, factor6, serial_number ). For some reason, it is not picking up on the key and just INSERTing instead of UPDATEing. Can anyone offer an explanation why? What am I missing?

Here is the table create statement:

CREATE TABLE `inventory` (
    `stockid` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `productid` int(11) unsigned NOT NULL,
    `factor1` int(11) unsigned NOT NULL,
    `factor2` int(11) unsigned NOT NULL,
    `factor3` int(11) unsigned NOT NULL,
    `factor4` varchar(8) NOT NULL,
    `factor5` decimal(10,2) NOT NULL,
    `factor6` enum('A','B','C','D','NEW') NOT NULL,
    `quantity` int(11) NOT NULL,
    `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `serial_number` varchar(11) DEFAULT NULL,
    PRIMARY KEY (`stockid`),
    UNIQUE KEY `serial_number` (`serial_number`),
    UNIQUE KEY `productid_2` (`productid`,`factor1`,`factor2`,`factor3`,`factor4`,`factor5`,`factor6`,`serial_number`),
    KEY `productid` (`productid`),
    KEY `factor1` (`factor1`),
    KEY `factor2` (`factor2`),
    KEY `factor3` (`factor3`),
    CONSTRAINT `books_stock_ibfk_2` FOREIGN KEY (`productid`) REFERENCES `produx_products` (`productid`),
    CONSTRAINT `books_stock_ibfk_5` FOREIGN KEY (`factor1`) REFERENCES `table_factor1` (`factorid`),
    CONSTRAINT `books_stock_ibfk_6` FOREIGN KEY (`factor2`) REFERENCES `table_factor2` (`factorid`),
    CONSTRAINT `books_stock_ibfk_7` FOREIGN KEY (`factor3`) REFERENCES `table_factor3` (`factorid`)
) 
ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1

More in-depth:

The purpose of this table is to hold stock quantities. I think this is pretty straight forward. The factors that separate these quantities are as follows:

• factor1 = storeid (the unique indentifier for the store that ownes this quantity).

• factor2 = supplierid (the unique indentfier for the supplier that we got the quantity from)

• factor3 = warehouseid (unique identifier for the warehouse where it resides)

• factor4 = locationid (unique string for the location. Its physically painted on the shelf)

• factor5 = cost (what we paid for each of the quantity)

• factor6 = condition (enum ['NEW','USED','RENTAL','PREORDER']. The first three are easy, the fourth is for quantites we ordered, want to sell, but have not received it yet.)

I know this is a hefty key but I am forced to keep it this way. I have had many suggestion to move cost or condition to the product table. I cannot do this. The cost isn't always the same since we buy a lot from auctions or other places with very variable costs and conditions.

I hope this helps more to explain what I am trying to do.

Answer 1

Mysql allows multiple NULLs in an unique constraint.In your serial_number column replace NULL with a value and the constraint is triggered,see:

http://sqlfiddle.com/#!2/9dbd19/1

a UNIQUE index permits multiple NULL values for columns that can contain NULL

Docs

Make the column NOT NULL and use '' which is empty.