Oracle SQL Constraint where clause

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.

sql oracle constraints where clause

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.

sql oracle constraints where clause

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?

CodeHunter