mySQL 可以根据另一列将特定列定义为 UNIQUE 吗？

发布于 2024-11-28 06:32:49 字数 752 浏览 1 评论 0原文

假设我有一张表：

  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”是primary，AI。但是，我希望能够根据“ml_id”和“telco”的父级添加几行不重复的“指示符”。例如：

"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”的每个实例，我希望能够拥有唯一且唯一的“指示符”。有道理吗？

谢谢

原文

Suppose I have a table:

  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

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"

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

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

堇色安年 2024-12-05 06:32:49

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

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

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

回复收藏 0 原文