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.
Danke, ich hatte es aus einem Gefühl heraus erst mal mit XOR probiert, dann habe ich diesen Text gefunden.
Funktioniert einwandfrei!
Freut mich das ich helfen konnte ;)
Es geht natürlich einfacher auch nur NVL:
CHECK (NVL(COL1,0)+NVL(COL2,0)+NVL(COL3,0)=1));