/* We have several protocols for the deathstar  but its important we only have one active protocol  at a time */createtabledeathstar_protocols(idintegernotnullprimarykey,labelvarchar2(256),alert_levelvarchar2(16)notnull,defense_modevarchar2(32)notnull,power_levelnumber(5,2)notnull);insertintodeathstar_protocolsvalues(1,'Everything easy','LOW','BE_KIND',80);insertintodeathstar_protocolsvalues(2,'Be careful','MEDIUM','BE_SUSPICIOUS',90);insertintodeathstar_protocolsvalues(3,'OMG the rebels!','VERY HIGH','SHOOT_FIRST_ASK_LATER',120);select*fromdeathstar_protocols;/* To make sure there is only one possibly  active protocol, we can use basic relational modeling  in combination with constraints */createtabledeathstar_protocol_active(idintegernotnullprimarykey,only_onenumber(1)default1notnull,-- ID is also foreign keyconstraintdeathstar_prot_act_fkforeignkey(id)referencesdeathstar_protocols(id)ondeletecascade,-- Make sure there can only be one rowconstraintdeathstar_prot_act_uqunique(only_one),-- by limiting the possible value of the-- helper-columnconstraintdeathstar_prot_act_chkcheck(only_one=1));/* This also means the technique is usable in  every relational database with check-constraints */insertintodeathstar_protocol_active(id)values(1);-- We cannot have more than one active protocolinsertintodeathstar_protocol_active(id)values(2);/* We can even have a view which shows  the active protocol */createviewv_deathstar_protocolsasselectprot.id,label,alert_level,defense_mode,power_level,coalesce(active.only_one,0)is_activefromdeathstar_protocolsprotleftouterjoindeathstar_protocol_activeactiveonprot.id=active.id;select*fromv_deathstar_protocols;updatedeathstar_protocol_activesetid=2;select*fromv_deathstar_protocols;