how to constrain a database table so only one row can have a particular value in

how to constrain a database table so only one row can have a particular value in  using -'oracle,triggers,constraints'

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.

asked Sep 14, 2015 by MadStein
0 votes

7 Answers

0 votes

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.

answered Sep 14, 2015 by FaustinoMcGu
0 votes

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.

answered Sep 14, 2015 by KelseyDebenh
0 votes

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

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.

answered Sep 14, 2015 by ChaScully
0 votes

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
answered Sep 14, 2015 by CoraHolroyd
0 votes

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');
answered Sep 14, 2015 by JaiChatfield
0 votes

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.

answered Sep 14, 2015 by Deloras95Vco
0 votes

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.

answered Sep 14, 2015 by MarcoUFRijh