Oracle Check Constraint: 3 Felder und nur eins darf gesetzt sein (quasi XOR)

Ich brauchte letztens ein Oracle Check Constraint, um zu prüfen, dass von 3 Feldern in denen IDs stehen, genau eins gesetzt ist, das heißt, es dürfen weder zwei oder alle drei Felder gefüllt sein, noch keins. Immer genau eins. Nur wie erledigt man das? Foreign Keys sind hier nicht wirklich geeignet, man muss sich also selber was ausdenken. Zum Glück gibt es ja nette Funktionen und die Constraints, hier nutzen wir eine Check Constraint:

SQL> CREATE TABLE  TEST_TABLE
  2  (COL1 VARCHAR2(10),
  3   COL2 VARCHAR2(10),
  4   COL3 VARCHAR2(10),
  5   SOME_VAL VARCHAR2(10),
  6   CONSTRAINT CHECK_NULL
  7   CHECK (NVL2(COL1,1,0)+NVL2(COL2,1,0)+NVL2(COL3,1,0)=1));
 
TABLE created.
 
SQL>  INSERT INTO test_table VALUES (NULL,NULL,NULL,'ww');
 INSERT INTO test_table VALUES (NULL,NULL,NULL,'ww')
*
ERROR at line 1:
ORA-02290: CHECK CONSTRAINT (SCOTT.CHECK_NULL) violated
 
 
SQL> INSERT INTO test_table VALUES (NULL,'1','dsdsd','ww');
INSERT INTO test_table VALUES (NULL,'1','dsdsd','ww')
*
ERROR at line 1:
ORA-02290: CHECK CONSTRAINT (SCOTT.CHECK_NULL) violated
 
 
SQL> INSERT INTO test_table VALUES (NULL,NULL,'1','ww');
 
1 ROW created.
 
SQL>

Wie man sehen kann funktioniert unsere Check Constraint NVL2(COL1,1,0)+NVL2(COL2,1,0)+NVL2(COL3,1,0)=1 perfekt. NVL2 prüft einen gegebenen Wert auf NULL und liefert entsprechend entweder den 2. Parameter zurück (Wert != NULL) oder den 3. Parameter der Funktion (Wert == NULL). Addiert man diese Werte auf, muss das Ergebnis 1 lauten, falls genau ein Feld gefüllt ist, jedes andere Ergebnis zeigt einen Fehler an. Durch diesen kleinen Trick kann man das Problem elegant lösen :).

Hinweis: Statt NVL2 kann man auch DECODE nutzen.

3 Gedanken zu „Oracle Check Constraint: 3 Felder und nur eins darf gesetzt sein (quasi XOR)

  • 18. Mai 2010 um 10:38
    Permalink

    Danke, ich hatte es aus einem Gefühl heraus erst mal mit XOR probiert, dann habe ich diesen Text gefunden.
    Funktioniert einwandfrei!

    Antworten
  • 7. Februar 2013 um 14:00
    Permalink

    Es geht natürlich einfacher auch nur NVL:
    CHECK (NVL(COL1,0)+NVL(COL2,0)+NVL(COL3,0)=1));

    Antworten

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Durch die weitere Nutzung der Seite stimmst du der Verwendung von Cookies zu. Weitere Informationen zum Datenschutz...

Die Cookie-Einstellungen auf dieser Website sind auf "Cookies zulassen" eingestellt, um das beste Surferlebnis zu ermöglichen. Wenn du diese Website ohne Änderung der Cookie-Einstellungen verwendest oder auf "Akzeptieren" klickst, erklärst du sich damit einverstanden.

Schließen