sql唯一约束2列组合 [英] sql unique constraint on a 2 columns combination
问题描述
如何为两列中的两个值组合创建唯一约束。
意义
column1 column2
2 1
约束禁止
column1 column2
1 2
如果你的数据库允许索引中的表达式,你可以这样做(ANSI SQL):
your_table上的CREATE UNIQUE INDEX(最少(column1,column2)
,最大(column1,column2));
请注意,这是唯一的索引而不是唯一约束。大多数DBMS的唯一区别是,您不能将唯一索引作为外键的目标,否则它们具有相同的目的。
如果您的DBMS没有 least()
或 greatest()
,可以使用CASE表达式替换:
在your_table上创建唯一索引
(case column1< column2 then column1 else column2 end,
case column2> column1然后column2 else column1 end));
How can you create a unique constraint on a combination of two values in two columns.
meaning
column1 column2
2 1
looking for the constraint to disallow
column1 column2
1 2
If your database allows expressions in an index you can do something like this (ANSI SQL):
CREATE UNIQUE INDEX on your_table (least(column1, column2)
, greatest(column1, column2));
Note this is a unique index not a unique constraint. The only difference for most DBMS is that you can't have a unique index as the target of a foreign key, but otherwise they serve the same purpose.
If your DBMS does not have least()
or greatest()
you can replace that using a CASE expression:
create unique index on your_table
(case column1 < column2 then column1 else column2 end,
case column2 > column1 then column2 else column1 end));
这篇关于sql唯一约束2列组合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!