sql - Create a table in Oracle using the boolean value (true) as an unique index field -

imagine table card_service key(id_number, begin_date, service_type), end_date , is_active.

the table card_service allows single id_number have multiples registers same service not want to happen.

i change table card_service to: key(id_number, service_type),begin_date, end_date , is_active , can have 1 single service type per id_number not keep previous services set false in is_active field is_active loses use.

so know if possible set value of boolean unique field in table in order create table accepts new entries if there no active service in specific service.

best regards

if i'm following want, use unique function-based index this:

create table card_service (     id_number number, begin_date date, service_type number, end_date date,      is_active varchar2(5),     constraint ck_is_active check (is_active in ('true', 'false')) );  table created.  create unique index ui_card_service on card_service (     case when is_active = 'true' id_number else null end,     case when is_active = 'true' service_type else null end );  index created. 

null values aren't included in index, you'll have index 'entries' true records, , applying case statement both fields means result still unique on 2 columns.

trying insert 2 records same id_number , service_type, both is_active set true, fails:

insert card_service values (1, date '2013-01-01', 1, null, 'true');  1 row created.  insert card_service values (1, date '2013-01-02', 1, null, 'true');  insert card_service values (1, date '2013-01-02', 1, null, 'true') * error @ line 1: ora-00001: unique constraint (scott.ui_card_service) violated 

updating existing record false first allows new true added;

update card_service set is_active = 'false' id_number = 1 , service_type = 1 , is_active = 'true';  1 row updated.  insert card_service values (1, date '2013-01-02', 1, null, 'true');  1 row created. 

and can keep doing that:

update card_service set is_active = 'false' id_number = 1 , service_type = 1 , is_active = 'true';  1 row updated.  insert card_service values (1, date '2013-01-03', 1, null, 'true');  1 row created. 

you can add true records other combinations:

insert card_service values (1, date '2013-01-04', 2, null, 'true'); insert card_service values (2, date '2013-01-05', 1, null, 'true'); insert card_service values (2, date '2013-01-06', 2, null, 'true'); 

so end with:

select * card_service;   id_number begin_dat service_type end_date  is_ac ---------- --------- ------------ --------- -----          1 01-jan-13            1           false          1 02-jan-13            1           false          1 03-jan-13            1           true          1 04-jan-13            2           true          2 05-jan-13            1           true          2 06-jan-13            2           true 

it more normal keep separate history table (presumably old) records though, main table ever has current records (whether true or false).