John Smithv1 John Smithv1 - 2 years ago 85
SQL Question

Oracle SQL Constraint where clause

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!

Answer Source

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

Here's a DEMO.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download