Oracle Unique constraint on certain value

Question

I have created a table (test_table), that has two columns (test_IP, test_STATUS). The table can have any number of IPs, however, only one IP with the status "Active" can be allowed. Any number of "Inactive" status' may be allowed. For reference (Active = 1, Inactive = 2)

For example:

test_IP           test_STATUS
==============================
1.2.3.4            1   
1.2.3.5            2   
1.2.3.6            2   
1.2.3.7            2    
1.2.3.8            1 -- this should now fail (as there are multiple values containing 1 (Active))    

Is it possible to have a unique contraint on a specific value? If so can someone please assist me on how I can achieve this? Thanks in advance!

Answer 1

I would add virtual invisible column is_active:

alter table ip_list
   add is_active varchar2(1) 
       invisible 
       generated always as 
          (case when test_STATUS=1 then 'y' end) virtual;

alter table ip_list add constraint uq_active_ip unique (is_active);

Since it's invisible, it will not affect existing queries. You can get it only if you specify is_active in your query.

Full example:

SQL> create table ip_list(test_IP,test_STATUS)
  2  as
  3  select '1.2.3.4', 1 from dual union all
  4  select '1.2.3.5', 2 from dual union all
  5  select '1.2.3.6', 2 from dual union all
  6  select '1.2.3.7', 2 from dual ;

Table created.

SQL> alter table ip_list add is_active varchar2(1) invisible generated always as (case when test_STATUS=1 then 'y' end) virtual;

Table altered.

SQL> alter table ip_list add constraint uq_active_ip unique (is_active);

Table altered.

SQL> insert into ip_list(test_ip, test_status) values('1.2.3.8',1);
insert into ip_list(test_ip, test_status) values('1.2.3.8',1)
*
ERROR at line 1:
ORA-00001: unique constraint (XTENDER.UQ_ACTIVE_IP) violated

-- note that * does not return this new column:
SQL> select * from ip_list;

TEST_IP TEST_STATUS
------- -----------
1.2.3.4           1
1.2.3.5           2
1.2.3.6           2
1.2.3.7           2

-- but you can use it to filter active rows:
SQL> select * from ip_list where is_active='y';

TEST_IP TEST_STATUS
------- -----------
1.2.3.4           1

1 row selected.