MySQL在两列之间强制执行唯一性 [英] MySQL enforce uniqueness across two columns

问题描述

我正在尝试在MySQL中实现我以前从未听说过的东西(也许是，研究并没有太大帮助).

I'm trying to achieve something in MySQL that I have not heard is possible before (maybe it is, researching didn't help me much).

我想做的是在MySQL的两列中强制执行唯一性.我的意思不是设置UNIQUE(column1，column2)而是以下两个条件:

What I'm trying to do is enforce uniqueness in MySQL across two columns. What I mean by this is not setting UNIQUE(column1,column2) but the following two conditions:

1. 如果列1中存在一个值，则不能在列1中重复该值(与设置UNIQUE(column1)相同).
2. 如果一个值存在于任一列中，则该值不能存在于另一列中.

因此，对于数据集{column1，column2}，如果已经存在{1,2}，{3,4}，{5,6}数据，那么这两列都不能具有以上任何一项新数据的数据项，即新数据项{x，y}，其中x = NOT {column1} AND y = NOT {column2} AND x！= y

Hence, for the data set {column1,column2}, if {1,2}, {3,4}, {5,6} are data already present, then neither of the two columns can have any of the above data items for new data,i.e. new data item {x,y} where x=NOT{column1} AND y=NOT{column2} AND x!=y

这可能吗?请在这里帮助我.谢谢.

Is this possible? Please help me out here. Thank you.

推荐答案

这可能是一个矫kill过正的方法，但是您可以将column1和column2存储在单独的表中.

This might be an overkill, but you can store column1 and column2 in a separate table.

假设您的桌子是

create table items (
    id int primary key,
    column1 int,
    column2 int
);

带有数据:

id | column1 | column2
---|---------|--------
 1 |       1 |       2
 1 |       3 |       4
 1 |       5 |       6

您可以将架构更改为

create table items (
    id int primary key
);

create table item_columns (
    item_id int,
    position int,
    val int,
    primary key (item_id, position),
    unique key (val),
    foreign key (item_id) references items(id)
);

带有数据:

item_id | position | val
--------|----------|----
      1 |        1 |   1
      1 |        2 |   2
      2 |        1 |   3
      2 |        2 |   4
      3 |        1 |   5
      3 |        2 |   6

您可以使用来模拟旧模式

You can simulate the old schema with

select i.id, c1.val as column1, c2.val as column2
from items i
left join item_columns c1
    on  c1.item_id = i.id
    and c1.position = 1
left join item_columns c2
    on  c2.item_id = i.id
    and c2.position = 2

如果愿意，可以在视图中使用它.

You can use it in a view if you like.

演示: http://rextester.com/PPBT42478

要确保position列的完整性，可以将其设为positions表的外键，该表仅包含值1和2.您也可以使用ENUM('1', '2')，但是ENUM始终允许使用空字符串作为值.

To guaranty the integrity for the position column, you can make it a foreign key to a positions table, which will only contain the values 1 and 2. You could also use ENUM('1', '2'), but ENUM always allow an empty string as a value.

这篇关于MySQL在两列之间强制执行唯一性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！