How to constrain a database table so only one row can have a particular value in a column?
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.
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.
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.