Constraints bei temporalen Daten

Für temporale Datenhaltungen werden meist zwei Timestamp-Attribute (validFrom und validUntil) verwendet. Diese erlauben die Diskriminierung auf Werte, die zu einem bestimmten Zeitpunkt Gültigkeit aufgewiesen haben:

 
SELECT 
  * 
FROM DATA 
WHERE 
  validfrom < to_date('2011-09-21', 'yyyy-mm-dd') 
  AND (
      validuntil > to_date('2011-09-21', 'yyyy-mm-dd')
      OR validuntil IS NULL
      )
 

Wir setzen dabei voraus, dass folgende Bedingungen stets gültig sind:

  • validfrom < validuntil, um einen positive Gültigkeitsdauer vorliegen zu haben
  • oder validuntil = null für einen aktuell gültigen Wert,
  • sowie dass der Schlüsselkandidat in Kombination mit validfrom und validuntil eindeutig ist,
  • und dass für einen Schlüsselkandiaten nur einmal ein NULL-Wert in validuntil vorliegt.

Mit Unique-Constraints ist die letzte der Bedingungen nicht zu prüfen, solange NULL zulässig ist. Das hängt mit der Tatsache zusammen, dass NULL bei Indizes unberücksichtigt bleibt. Auflösen lässt sich dieses Problem entweder durch Verwendung eines Triggers, welcher vor einem Update oder Insert eine Prüfung vornimmt. Oder dann, indem auf validfrom ebenfalls ein NOT-NULL-Constraint gesetzt wird. Wir müssen dann anstelle von NULL zur Auszeichnung eines noch gültigen Wertes einen Wert in der Vergangenheit festlegen. Es bietet sich an, dafür den Start der neuen Zeitrechnung einzusetzen (01.01.0001 00:00:00).

Eine beispielhafte Abfrage sieht dann wie folgt aus:

 
SELECT 
  * 
FROM DATA 
WHERE 
  validfrom < to_date('2011-09-21', 'yyyy-mm-dd') 
  AND (
      validuntil > to_date('2011-09-21', 'yyyy-mm-dd')
      OR validuntil = to_date('0001-01-01', 'yyyy-mm-dd')
      )
 

Zur Abfrage ausschliesslich aktueller Daten reicht eine Diskriminierung auf Einträge aus, bei welchen validUntil kleiner ist als validFrom:

 
SELECT 
  * 
FROM DATA 
WHERE 
  validfrom > validuntil
 

Dann sind wir in der Lage, die erforderlichen Constraints wie folgt zu setzen (im vorliegenden Fall besteht der Schlüsselkandiat ausschliesslich aus dem Attribut FK):

 
CREATE TABLE DATA (
  ID NUMBER NOT NULL, 
  FK NUMBER NOT NULL, 
  VALIDFROM TIMESTAMP(6) NOT NULL, 
  VALIDUNTIL TIMESTAMP(6) NOT NULL, 
  CONSTRAINT DATA_PK PRIMARY KEY (ID ) ENABLE 
);
 
ALTER TABLE DATA
ADD CONSTRAINT DATA_UK1 UNIQUE 
(FK, VALIDFROM, VALIDUNTIL) ENABLE;
 
ALTER TABLE DATA
ADD CONSTRAINT DATA_UK2 UNIQUE 
(FK, VALIDUNTIL) ENABLE;
 
ALTER TABLE DATA
ADD CONSTRAINT DATA_CHK1 CHECK 
(VALIDFROM < VALIDUNTIL OR VALIDUNTIL = TO_DATE('0001-01-01', 'yyyy-mm-dd'))
ENABLE;
 

31.12.9000 oder 01.01.0001?

Die Verwendung eines Datums in der Vergangenheit anstelle von NULL mag auf den ersten Blick ungünstig erscheinen. Es ist sicher etwas unintuitiv. Aus der Perspektive der Abfrage bietet sich an, ein beliebiges Datum weit in der Zukunft zu wählen, da damit alle nicht-aktullen Daten mit einer einfachen Between-Klausel diskriminiert werden können. Die hier getroffene Wahl hat semantische Gründe; die ausdrücklich zugelassene Ausnahme, dass validuntil kleiner sein darf als validfrom, wenn es sich um den 01.01.0001 handelt, macht einen Eintrag mit unbestimmter Gültigkeitsdauer unterscheidbar von Einträge mit dauerhafter Gültigkeit oder solchen, mit einer festgelegten Gültigkeit, die in der Zukunft endet.

Überschneidende Gültigkeitsperioden

Eine mit Constraints alleine (zurzeit, Oracle 11g) nicht sicherzustellende Bedingung ist, dass die Gültigkeitsperioden keine Überschneidungen aufweisen dürfen. Eine solche Prüfung muss entweder mit beforeInsert- und beforeUpdate-Triggers sichergestellt werden. Wird auf eine solche verzichtet, sollte man die Variante mit einem Datum in der Vergangenheit (01.01.0001) erwägen, da diese mindestens für aktuell gültige Werte stets nur einen Eintrag zulässt.

Ticker

03.01.12  -  Die Pause ist vorüber und wir sind mit neuem Tatendrang wieder an der Arbeit. Willkommen im 2012!

23.12.11  -  Wir wünschen allen frohe Festtage und einen guten Rutsch ins Neue Jahr!