Oracle SQL Constraint where clause

Question

I have the table Tester on oracle with the following columns:

TesterID
TesterName
IsDefault
Application_ID

TesterID is the primary key. Now I want that there can only be one Default Tester, which means only one Tester can have the calues IsDefault =Y at an ApplicationID.

I tried it with a constraint:

alter table Tester add constraint Tester_ISDEFAULT UNIQUE(IsDefault,Application_ID);

Is it possible to make the unique key on where isdefault= Y?

Thanks for help!

João Silva · Accepted Answer · 2012-08-29T12:10:17.820

7

Not with a UNIQUE constraint. However, you can use a UNIQUE INDEX instead:

CREATE UNIQUE INDEX ApplicationId_Default_Y ON tester (
  CASE WHEN IsDefault = 'Y'
       THEN ApplicationId
       ELSE NULL
  END
);

Here's a DEMO.

edited Aug 29 '12 at 12:10

answered Aug 29 '12 at 12:05

João Silva

89,303
29
152
158

Hello, It works, but I don't know how. You make the IsDefault to an index? What means then applicationid and else null? – John Smithv1 Aug 29 '12 at 12:48
Basically, when `IsDefault = 'Y'`, you store its application id in the index. Thus, since it's a *unique* index, if you try to insert again the same application id in the index with `IsDefault = Y`, it will throw an exception (because that application id was already in the index). – João Silva Aug 29 '12 at 12:49
Just expanding Joao's explanation - this is usually called a "function-based index". It takes advantage of the fact that if the entire key is NULL, Oracle does not store anything in the index; so this index only stores ApplicationId when IsDefault = 'Y'. Furthermore, it's a unique index, so it guarantees that there will only be one row for any given ApplicationId where IsDefault = 'Y'. – Jeffrey Kemp Aug 30 '12 at 03:15

score 1 · Answer 2 · answered Aug 29 '12 at 12:03

You can do this with a function-based unique index, not a constraint as such:

create unique index tester_isdefault on tester 
  (case when isdefault='Y' then application_id end);

Since Oracle doesn't created index entries for keys that are all null, only rows where isdefault='Y' will appear in the index.

score 0 · Answer 3 · answered Aug 29 '12 at 12:02

0

That constraint won't work as it would mean you can only have two rows for each Application_ID - one with IsDefault=0 and the other with IsDefault=1.

You could enforce this logic with a trigger. Or, why not just enforce it in your application logic?

answered Aug 29 '12 at 12:02

Martin Wilson

3,386
1
24
29

2

using the trigger you face with mutation error else you have to invent kind of trick to defeat it. so unique FBI – Alexander Tokarev Aug 29 '12 at 12:05

score 0 · Answer 4 · answered Aug 29 '12 at 12:03

0

create unique index tester_ui_1 on tester(decode(is_default, 'Y', 0, tester_id), application_id)

answered Aug 29 '12 at 12:03

Alexander Tokarev

1,000
7
16

Oracle SQL Constraint where clause

4 Answers4

Linked