mysql两列唯一,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).

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:

If a value exists in column1, it cannot be repeated in column1 (same as setting UNIQUE(column1)).

If a value exists in either column, it cannot exist in the other column.

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.

解决方案

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

Let's say your table is

create table items (

id int primary key,

column1 int,

column2 int

);

with data:

id | column1 | column2

---|---------|--------

1 | 1 | 2

1 | 3 | 4

1 | 5 | 6

You can change your schema to

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)

);

with data:

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.

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.