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.

Gershwin

Oh dear - nulls not permitted - that changes things a little - now you have to go with the function-based index (@Tony Andrews). Still avoid triggers and autonomous transactions. – Aeromechanics

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

Michelsen

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

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.

Indene

Nothing kludgy about it - that's the way to go. +1 – Aeromechanics

You'll also need to add a check constraint on the table so it doesn't permit anything other than "YES" or null. – Aeromechanics

if you want it to appear nice, you could probably wrap a view with NVL around it as well, then you would get your Y/N – Fly

Well, using NULL is kludgy if you want to join on it. It may be a kludge worth making, but it's still a design compromise away from a more correct approach. – Wandy

Answer 3

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.

Piercing

The question asked there is slightly different to this, in Tom's question, the table could have multiple "Y", but only 1 per country. As I read it, in this example, the table can have only 1 Yes, in which case the index solution seems to work. But yes, AskTom is a must for Oracle database. – Fly

I agree. If Tom was asked this question I'd guess he'd definitely go for the index solution. – Aeromechanics

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

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

Kimbro

-1 for a trigger0based solution. They never work well for enforcing table-level constraints – Wandy

Answer 5

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

Aeromechanics

I think that as long as NULL is suitable as the "not yes" value this method has the advantage over the FBI method because the constraints can at least be leveraged by the optimizer. I think that queries with predicates on yesorno='YES' and yesorno is null will get better cardniality estimates. – Wandy

Since there is only ever going to be 1 row with "YES" and every other row with a null, then you'll get the index used to find the "YES" row and a full scan (quite rightly) to get everything else (the "NO"s). – Aeromechanics

Sure -- it's just a question of whether replacing "No" with Null is a worthwhile compromise to achieve this aim. – Wandy

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

Fundy

No filtered indexes, but FBIs offer a more flexible (if arguably less concise) way to do the same thing. – Wanting

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.

Tiossem

diffcult to maintain in a consistent environment – Wandy