CODEWITHSUNDEEP
databaseoracle-databaserowdefault
Hussain Ashruf
Hussain Ashruf

Reputation: 441

Implement a 'default' flag that can only be set on a single row in a given table

How to implement a 'default' flag that can only be set on a single row in a given table? at database level and what could be it's datatype in Oracle.

Or would it need to set up such a condition in the application, but not at database level?

Upvotes: 1

Views: 267

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use a unique function-based index, something like:

create unique index uniq_default on your_table
  (case when default_flag = 'Y' then 'Y' else null end);

Null values aren't indexed, so the case statement evaluates to either a single value or null; and because it's unique yhe single value can only appear once.

SQL Fiddle demo showing an error trying to insert a second row with the flag set to Y. Same thing happens if you try to update a value to Y to create a duplicate.

Or you could use a virtual column with the same logic and a unique constraint, which is much the same thing.

Upvotes: 4

Related Questions