mySQL可以基于另一列将特定列定义为UNIQUE吗? [英] Can mySQL define specific columns as UNIQUE based on another column?

问题描述

假设我有一张桌子:

  CREATE TABLE `ml_vendor_refs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ml_id` int(11) NOT NULL,
  `ven_id` int(11) NOT NULL,
  `designator` int(4) NOT NULL,
  `telco` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

如您所见，"id"是主要的，是AI.但是，我希望能够基于"ml_id"和"telco"的父级添加几行我不能重复的标识符". 例如:

As you can see, "id" is primary and AI. However, I want to be able to add several rows of 'designator' that my not duplicate based on the parent of 'ml_id' and 'telco'. For example:

"id"    "ml_id" "ven_id"    "designator"    "telco"
"1"     "5144"    "3"            "1"           "0"
"2"     "5144"    "7"            "2"           "0"
"3"     "5144"    "44"           "3"           "0"
"4"     "5144"    "49"           "4"           "0"

对于"ml_id"和布尔"telco"的每个实例，我希望能够拥有唯一且唯一的指定符".有道理吗?

for every instance of "ml_id" and the boolean "telco", I want to be able to have unique and ONLY unique "designators". Make sense?

谢谢

推荐答案

您可以拥有一个UNIQUE索引，该索引跨越多个列.结果将是一个限制，使得相同的值可以多次出现在一个列中，但是相同的值组合不能出现在指定的列中.

You can have a UNIQUE index than spans multiple columns. The result would be a restriction such that the same value can appear in one column multiple times but the same combination of values cannot appear in the specified columns.

CREATE TABLE `ml_vendor_refs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ml_id` int(11) NOT NULL,
  `ven_id` int(11) NOT NULL,
  `designator` int(4) NOT NULL,
  `telco` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `designators` (`designator`,`ml_id`,`telco`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

这篇关于mySQL可以基于另一列将特定列定义为UNIQUE吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！