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

Imagine a table CARD_SERVICE with KEY(Id_number, Begin_date, Service_Type), End_date and Is_active.

The table CARD_SERVICE allows a single Id_number have multiples registers with the same service what I do not want to to happen.

I could change the table CARD_SERVICE to: KEY(Id_number, Service_Type),Begin_date, End_date and Is_active and now I can have only one single service type per id_number however I could not keep the previous services that are set as False in Is_active so the field is_active loses its use.

So I would like to know if it is possible to set a value of a boolean as an unique field in a table in order to create a table that only accepts new entries if there is no active service in a specific service.

best regards

Answers


If I'm following what you want, you could use a unique function-based index to do 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' then id_number else null end,
    case when is_active = 'TRUE' then service_type else null end
);

Index created.

Null values aren't included in the index, so you'll only have index 'entries' for the TRUE records, and applying the case statement to both fields means the result will still be unique over those two columns.

Trying to insert two records for the same id_number and service_type, both with is_active set to TRUE, fails:

insert into card_service values (1, date '2013-01-01', 1, null, 'TRUE');

1 row created.

insert into card_service values (1, date '2013-01-02', 1, null, 'TRUE');

insert into card_service values (1, date '2013-01-02', 1, null, 'TRUE')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UI_CARD_SERVICE) violated

Updating the existing record to FALSE first allows a new TRUE to be added;

update card_service set is_active = 'FALSE'
where id_number = 1 and service_type = 1 and is_active = 'TRUE';

1 row updated.

insert into card_service values (1, date '2013-01-02', 1, null, 'TRUE');

1 row created.

And you can keep doing that:

update card_service set is_active = 'FALSE'
where id_number = 1 and service_type = 1 and is_active = 'TRUE';

1 row updated.

insert into card_service values (1, date '2013-01-03', 1, null, 'TRUE');

1 row created.

You can add TRUE records for other combinations:

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

So you end up with:

select * from 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 would be more normal to keep a separate history table with the (presumably old) records though, so your main table only ever has current records (whether TRUE or FALSE).


Need Your Help

EC2 in VPC connectivity issues

networking amazon-web-services amazon-ec2 cloud amazon-vpc

I have a few ec2s in a public subnet (ie route table connected to the IGW) of my VPC.

Saving State of a ToggleButton in a RecyclerView in android

java android recyclerview

I have a recyclerView that gets data from a url that outputs json and loads a image using picasso and some other datas. Each item in the view has a image, name, date and a ToggleButton.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.