Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

Samuel Nitsche
Samuel Nitsche

Posted on • Originally published atcleandatabase.wordpress.com on

     

#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.

IDLABELALERT_LEVELDEFENSE_MODEPOWER_LEVEL
1Everything easyLOWBE_KIND80
2Be carefulMEDIUMBE_SUSPICIOUS90
3OMG the rebels!VERY HIGHSHOOT_FIRST_ASK_LATER120

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 */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;
Enter fullscreen modeExit fullscreen mode

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

Top comments(0)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

Curiosity-driven software-developer, 10x underpants.Striving for harm-reduction.We don't need more rockstars, we need more mentors.
  • Location
    Germany
  • Work
    Software Developer at Smart Enterprise Solutions GmbH
  • Joined

More fromSamuel Nitsche

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp