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.