如何约束数据库表，以便只有一行可以在列中具有特定值？

Using Oracle, if a column value can be 'YES' or 'NO' is it possible to constrain a table so that only one row can have a 'YES' value?

使用Oracle,如果列值可以是“是”或“否”,是否可以约束表,以便只有一行可以具有“是”值?

I would rather redesign the table structure but this is not possible.

我宁愿重新设计表结构,但这是不可能的。

[UDPATE] Sadly, null values are not allowed in this table.

[UDPATE]遗憾的是,此表中不允许使用空值。

7 个解决方案

#1

Use a function-based index:

使用基于函数的索引:

create unique index only_one_yes on mytable
(case when col='YES' then 'YES' end);

Oracle only indexes keys that are not completely null, and the CASE expression here ensures that all the 'NO' values are changed to nulls and so not indexed.

Oracle仅索引不完全为null的键,此处的CASE表达式确保所有“NO”值都更改为空,因此不会编入索引。

#2

This is a kludgy hack, but if the column allows NULLs, then you could use NULL in place of "NO" and use "YES" just as before. Apply a unique key constraint to that column, and you'll never get two "YES" values, but still have many NOs.

这是一个kludgy hack,但如果列允许NULL,那么您可以使用NULL代替“NO”并像以前一样使用“YES”。对该列应用唯一键约束,您将永远不会得到两个“YES”值,但仍然有很多NO。

Update: @Nick Pierpoint: suggested adding a check constraint so that the column values are restricted to just "YES" and NULL. The syntax is all worked out in his answer.

更新:@Nick Pierpoint:建议添加一个检查约束,以便将列值限制为“YES”和NULL。语法在他的回答中得到了解决。

#3

You will want to check a Tom Kyte article with exactly this question being asked and his answer:

您将要查看Tom Kyte的文章,正好询问这个问题和他的答案:

http://tkyte.blogspot.com/2008/05/another-of-day.html

Summary: don't use triggers, don't use autonomous transactions, use two tables.

总结:不使用触发器,不使用自治事务,使用两个表。

If you use an Oracle database, then you MUST get to know AskTom and get his books.

如果您使用Oracle数据库,那么您必须了解AskTom并获取他的书籍。

#4

It doesn't work on the table definition.

它不适用于表定义。

However, if you update the table using a trigger calling a stored procedure, you could make sure that only one row contains "YES".

但是,如果使用调用存储过程的触发器更新表,则可以确保只有一行包含“YES”。

Set all rows to "NO"

将所有行设置为“NO”

Set the row you want to YES

将您想要的行设置为YES

#5

Following on from my comment to a previous answer by yukondude, I'd add a unique index and a check constraint:

继我对yukondude之前的回答之后,我会添加一个唯一索引和一个检查约束:

create table mytest (
    yesorno varchar2(3 char)
);

create unique index uk_mytest_yesorno on mytest(yesorno);

alter table mytest add constraint ck_mytest_yesorno check (yesorno is null or yesorno = 'YES');

#6

Does Oracle support something like filtered indices (last week I heard that e.g. MSSQL2008 does)? Maybe you can define a unique key which applies only to rows with the value "Yes" in your column.

Oracle是否支持类似过滤索引的内容(上周我听说过MSSQL2008会这样做)?也许您可以定义一个唯一的键,该键仅适用于列中值为“是”的行。

#7

-2

I guess I'd use a second table to point to the appropriate row in your current table. That other table could be used to store values of other variables too too.

我想我会使用第二个表指向当前表中的相应行。另一个表也可用于存储其他变量的值。

#1