How to constrain a database table so only one row can have a particular value in a column?

Question

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?

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

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

Answer 1

Only using a trigger AFAIK.

Answer 2

So, only one row can be "YES", but many rows can be "NO" ?

Answer 3

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".

1. Set all rows to "NO"
2. Set the row you want to YES

Answer 4

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.

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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

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');

Answer 9

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

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.