#100CodeExamples – Only one active protocol

Sometimes you have the situation that you store several different variants of an entity in the database, but you have to be absolutely sure there is only one of these variants active at a time.

Examples could be different configurations or color schemes you can choose from, the active financial year in accounting-related scenarios or a default entry.

There’s a similar thing in the deathstar, which runs on one of several protocols.

To make sure, there is only ever one protocol active, the database developers use simple tools most relational databases provide:

/* We have several protocols for the deathstar
  but its important we only have one active protocol
  at a time
 */
create table deathstar_protocols (
  id integer not null primary key,
  label varchar2(256),
  alert_level varchar2(16) not null,
  defense_mode varchar2(32) not null,
  power_level number(5,2) not null
);

insert into deathstar_protocols
  values (1, 'Everything easy', 'LOW', 'BE_KIND', 80);
insert into deathstar_protocols
  values (2, 'Be careful', 'MEDIUM', 'BE_SUSPICIOUS', 90);
insert into deathstar_protocols
  values (3, 'OMG the rebels!', 'VERY HIGH', 
    'SHOOT_FIRST_ASK_LATER', 120);

select * from deathstar_protocols;

/* To make sure there is only one possibly
  active protocol, we can use basic relational modeling
  in combination with constraints
 */
create table deathstar_protocol_active (
  id integer not null primary key,
  only_one number(1) default 1 not null,
  -- ID is also foreign key
  constraint deathstar_prot_act_fk
    foreign key ( id )
    references deathstar_protocols ( id )
    on delete cascade,
  -- Make sure there can only be one row
  constraint deathstar_prot_act_uq
    unique ( only_one ),
  -- by limiting the possible value of the
  -- helper-column
  constraint deathstar_prot_act_chk
    check ( only_one = 1 )
);

/* This also means the technique is usable in
  every relational database with check-constraints
 */

insert into deathstar_protocol_active ( id ) values (1 );

-- We cannot have more than one active protocol
insert into deathstar_protocol_active ( id ) values ( 2 );

/* We can even have a view which shows
  the active protocol
 */
create view v_deathstar_protocols
  as
  select
    prot.id, label, alert_level, defense_mode, power_level,
    coalesce(active.only_one, 0) is_active
  from
    deathstar_protocols prot
    left outer join deathstar_protocol_active active
      on prot.id = active.id
;

select * from v_deathstar_protocols;

update deathstar_protocol_active set id = 2;

select * from v_deathstar_protocols;

You can run this example on LiveSQL, but it’s also possible on SQL Server and every other relational database with CHECK-constraints.