MySQL UNIQUE键不起作用

本文介绍了MySQL UNIQUE键不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在按库存表上的类型进行构建，该表通过6种不同的因素来跟踪库存.我正在使用类似这样的查询:

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;

inventory表具有一个唯一键(productid，factor1，factor2，factor3，factor4，factor5，factor6，serial_number).出于某种原因，它不是选择键，而是INSERT ing而不是UPDATE ing.谁能提供一个解释为什么?我想念什么?

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?

这是表创建语句:

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

更深入的了解:

此表的目的是保存库存数量.我认为这很简单.分隔这些数量的因素如下:

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(拥有此数量的商店的唯一标识符).

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

factor2 = providerid(供应商的唯一标识，我们从中获取了数量)

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

factor3 = Warehouseid(仓库所在的仓库的唯一标识)

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

factor4 = locationid(位置的唯一字符串.其物理上画在架子上)

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

factor5 =成本(我们为每个数量支付的费用)

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

factor6 =条件(枚举['NEW'，'USED'，'RENTAL'，'PREORDER'].前三个很简单，第四个是我们订购的数量，想要出售但没有数量的数量收到了.)

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.

inventory

MySQL UNIQUE键不起作用

问题描述

推荐答案