Datenkonsistenz mit Constraints und Indexen gewährleisten

Das heutige Thema des Blogs ist inspiriert von meiner DOAG WebSession am 09.12.2022 und den daraus resultierenden Rückfragen. Es geht um Datenintegrität, um diese zu gewährleisten, können Constraints im Datenmodell definiert werden. Der Vorteil von Constraints ist, dass diese im Kernel der Datenbank verarbeitet werden. Das bedeutet, die Prüfung kostet sehr wenig Resourcen, ist damit sehr schnell und vor allem muss man sich nicht um Themen wir konkurrierende Zugriffe Gedanken machen, denn darum kümmert sich ebenfalls die Datenbank. Lösungen zur Gewährleistung der Datenintegrität mit Triggern oder gar in der Applikationslogik sind deutlich fehleranfälliger, weniger performant und teilweise umgehbar. Wird die Integrität beispielsweise nur in der Applikation geprüft, besteht die Möglichkeit, inkonsistente Daten auf anderem Wege in die Datenbank einzufügen, über externe Schnittstellen zum Beispiel.

Nehmen wir folgendes einfaches Beispiel: Es soll eine Liste der Netzwerkgeräte mit IP-Adresse und Hostname verwaltet werden. Dazu gibt es einen Status, ob das Gerät noch aktiv ist oder ob es abgeschaltet wurde und damit der Vergangenheit angehört. Die Tabelle sieht daher wie folgt aus:

SQL> create table device_list (
  2    id       number generated always as identity,
  3    ip_addr  varchar2(15) not null,
  4    hostname varchar2(30) not null,
  5    status   varchar2(1)  not null
  6  );

Table DEVICE_LIST erstellt.

Ein kleiner Hinweis am Rande, wenn Spalten immer einen Wert enthalten müssen, so sollte man das der Datenbank auch mitteilen. Der Optimizer nutzt diese Information z.B. bei WHERE-Bedingungen mit "IS [NOT] NULL" um die Ausführung zu optimieren. Nun aber zur Datenintegrität, für den Status sind nur zwei Werte erlaub, 'A' wie Aktiv und 'I' wie Inaktiv. Weiterhin muss die IP-Adresse eine bestimmte Form haben und auch der Hostname darf nur bestimmte Zeichen enthalten. Das kann jeweils direkt über einen Constraint gewährleistet werden. 

SQL> alter table device_list add (
  2    constraint chk_status   check (status in ('A','I')),
  3    constraint chk_ip_addr  check (regexp_like(ip_addr, '^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$')),
  4    constraint chk_hostname check (regexp_like(hostname, '^[a-z0-9\-]+$','i'))
  5  );

Tabelle wurde geändert.

Der Check für den Status ist einfach. Um die IP-Adresse und den Hostname zu prüfen, bieten sich reguläre Ausdrücke an. Das Format fü IP-Adressen ist klar, der Hostname darf nur Buchstaben, Zahlen oder ein Minus enthalten. Schauen wir, ob das so funktioniert.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.1', 'wlan-router', 'A');

1 Zeile wurde erstellt.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1', 'printer', 'A');
insert into device_list(ip_addr, hostname, status)
*
FEHLER in Zeile 1:
ORA-02290: CHECK-Constraint (ORG_SUP.CHK_IP_ADDR) verletzt

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.10', 'printer', 'A');

1 Zeile wurde erstellt.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.20', 'laptop', 'A');

1 Zeile wurde erstellt.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.21', 'laptop_marco', 'A');
insert into device_list(ip_addr, hostname, status)
*
FEHLER in Zeile 1:
ORA-02290: CHECK-Constraint (ORG_SUP.CHK_HOSTNAME) verletzt


SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.21', 'laptop-marco', 'A');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from device_list
  2  ;

        ID IP_ADDR         HOSTNAME                       S
---------- --------------- ------------------------------ -
         1 192.168.1.1     wlan-router                    A
         2 192.168.1.10    printer                        A
         3 192.168.1.20    laptop                         A
         4 192.168.1.21    laptop-marco                   A

Man sieht also, dass der Check sowohl für die IP-Adressen als auch für die Hostnames funktioniert. Es können nur sinnvolle Werte eingefügt werden. So weit, so gut. Nun wollen wir aber auch dafür sorgen, dass die IP-Adresse eindeutig ist. Das darf aber wiederum nur für aktive Geräte gelten, ausgemusterte Geräte dürfen natürlich nicht mit in diese Prüfung fallen. 

Auch das lässt dich mit einem Trick realisieren. Wir machen uns zu Nutze, dass in einem Index NULL-Werte nicht mit gespeichert werden, sondern nur "echte" Werte. Ein Unique Index ist daher nur für tatsächliche Werte eindeutig, NULL-Werte können beliebig oft auftreten. Wir sorgen also dafür, dass nur die IP-Adressen von aktiven Geräten in so einen Unique Index aufgenommen werden. Das geht mit einem einfachen Function Based Index:

SQL> create unique index ix_unique_active_ip on device_list (case when status='A' then ip_addr else null end);

Index wurde erstellt.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.20', 'tablet', 'A');
insert into device_list(ip_addr, hostname, status)
*
FEHLER in Zeile 1:
ORA-00001: Unique Constraint (ORG_SUP.IX_UNIQUE_ACTIVE_IP) verletzt


SQL> update device_list set status='I' where ip_addr='192.168.1.20';

1 Zeile wurde aktualisiert.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.20', 'tablet', 'A');

1 Zeile wurde erstellt.

SQL> insert into device_list(ip_addr, hostname, status)
  2  values ('192.168.1.20', 'handy', 'A');
insert into device_list(ip_addr, hostname, status)
*
FEHLER in Zeile 1:
ORA-00001: Unique Constraint (ORG_SUP.IX_UNIQUE_ACTIVE_IP) verletzt

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from device_list;

        ID IP_ADDR         HOSTNAME                       S
---------- --------------- ------------------------------ -
         1 192.168.1.1     wlan-router                    A
         2 192.168.1.10    printer                        A
         3 192.168.1.20    laptop                         I
         4 192.168.1.21    laptop-marco                   A
         6 192.168.1.20    tablet                         A

  Wie man sieht, kann das neue Gerät erst eingefügt werden, nachdem das alte Gerät auf "inaktiv" gesetzt wurde und daher aus dem Unique Index entfernt wurde. Der Versuch, ein weiteres neues Gerät mit der gleichen IP-Adresse hinzuzufügen, schlägt dann wiederum fehl. Also genau das Verhalten, was wir erreichen wollten.

Fazit: Oftmals kann man viel mehr Regeln für die Datenintegrität direkt im Datenmodell und der Indizierung abbilden, als man vielleicht denkt. Und wenn das gelingt, erlangt man neben besserer Performance auch robustere Applikationen, wie wir finden.