Database Refactoring: “Introduce Column Constraint”

Dieser Blog-Eintrag ist dem Unterkapitel „Introduce Column Constraint“ in Kapitel 7 des Buchs „Refactoring Databases –  Evolutionary Database Design“ gewidmet.

Scott W. Ambler und Pramod J. Saldage stellen in ihrem 2006 erschienenen Buch Techniken und Vorgehensweisen für die agile Entwicklung von Datenbanken vor.

Ein Großteil des Buches besteht aus „Database Refactorings“. Dies sind kleine Änderungen am Datenbankschema, welche das Design verbessern, ohne die Semantik zu ändern. Änderungen, welche die Semantik verändern, werden hingegen Transformationen genannt.

Vermutlich hätte ich viele der Lektionen aus dem Buch nicht auf die harte Tour lernen müssen, wenn ich es schon früher gelesen hätte. Einige andere Refactorings wiederum hätten mir in früheren Projekten dabei geholfen, eine saubere Lösung zu implementieren, die mir noch dazu viel Zeit gespart hätte. An dieser Stelle möchte ich dem Buch also eine klare Empfehlung geben. Meine Lieblings-Refactorings sind:

Neben dem klaren Lob möchte ich aber auch nicht unerwähnt lassen, dass manche Refactorings aus meiner Sicht nicht ausführlich genug beschrieben sind. Eines davon ist „Introduce Column Constraint“ bei dem mir der „Access Program Update Mechanics“-Teil zu kurz ist. Weiters enthält die Darstellung keine Transition-Period zwischen Programm- und Schema-Update, welche aber in vielen Fällen nötig sein kann.

In folgendem Beispiel werde ich einen Unique Constraint für eine Spalte einfügen und dabei auf die verschiedenen Probleme eingehen die einem dabei unterkommen und Lösungen dafür aufzeigen. Ich verwende Transact-SQL in den SQL-Code-Snippets.

create table Country ( 
id bigint identity,
iso3166_alpha3 varchar(255), -- AUT, GER, ...
)

create table Company (
id bigint identity,
name varchar(255) not null, -- Microsoft, Oracle, Cenarion, ...
)

Bei beiden sollen Unique Constraints hinzugefügt werden. Die verschiedenen Probleme, die entstehen können, haben folgende Dimensionen:

  1. Sind die Daten derzeit unique oder können Sie bis zum Einspielen des Skripts non-unique werden?
  2. Kann man die Daten, automatisch oder manuell, unique machen?

Daraus ergeben sich folgende Szenarien.

  1. Die Spalte, die unique gemacht werden soll, ist derzeit unique. Es kann bis zum Einspielen eines Refactoring-Skripts auch nicht non-unique werden (weil z.B. keine Eingaben von Usern gemacht werden und der Code keine Duplikate einfügt).
  2. Die Spalte, die einen Unique Constraint erhalten soll, ist non-unique oder kann non-unique werden. Der Inhalt kann aber automatisch unique gemacht werden mit einem SQL-Skript.
  3. Die Spalte die einen Unique Constraint erhalten soll, ist non-unique oder kann non-unique werden. Der Inhalt kann nicht automatisch unique gemacht werden.
  4. Bestehende Einträge können zwar bereinigt werden aber es kann nicht erzwungen werden, dass keine neuen Einträge eingefügt werden können, welche den Constraint verletzen.
Szenario 1: Die Spalte ist und bleibt unique

Szenario 1 ist einfach. Die Länder-Tabelle ist ein gutes Beispiel dafür.

id                   iso3166_alpha3 
-------------------- ---------------
1 AUT
2 GER

Sofern die Tabelle nur real existierende Länder enthält ist garantiert, dass diese Daten eindeutig sind. Wenn die Tabelle nicht von Benutzern verändert werden kann bzw. es so unwahrscheinlich ist, dass ein Benutzer einen falschen Wert einfügt, kann in diesem Fall einfach der Unique Constraint hinzugefügt werden.

alter table Country add constraint UQ_iso3166_alpha3 unique(iso3166_alpha3)
Szenario 2: Die Spalte ist nicht unique, die Bereinigung aber automatisierbar

Bei Szenario 2 muss schon mehr getan werden. Angenommen die Company-Tabelle hat folgenden Inhalt.

Company-Tabelle

id                   name 
-------------------- -----------
1 Test
2 Test
3 Microsoft
4 Oracle

Hier kann man die ROW_NUMBER() Funktion von Microsoft Sql-Server einsetzen. Ich ändere alle Namen ab dem zweiten Vorkommen auf alter-Name + Nummer.

update co set co.name = r.name + cast(rownr as varchar)  
from Company co
join ( select id,name, ROW_NUMBER() over(PARTITION BY name ORDER BY ID) rownr from Company
) AS r ON co.id = r.id
where r.rownr > 1

Natürlich löst das nur dann das Problem, wenn die Duplikate anschließend von den Benutzern bereinigt werden können. Also wenn Benutzer einfach das Unternehmen „Test 2“ löschen können (hard delete oder soft delete), damit es nicht mehr stört. Wenn dies nicht der Fall ist, dann müssen vor einer Änderung der Daten für jeden Duplikats-Eintrag die ID des Eintrags bestimmt werden, der erhalten bleiben soll.

select *, (select MIN(id) from Company c where c.name = x.name group by c.name) as reference_id into CompanyDuplicates from ( 
select id,name, ROW_NUMBER() over(PARTITION BY name ORDER BY ID) rownr from Company
) as x where x.rownr > 1

Tabelle CompanyDuplicates

id                   name             rownr     reference_id 
-------------------- ---------------- --------- -------------
4 Test 2 1

Mit dieser Information können in Folge beispielsweise Datensätze, welche das Unternehmen „Test 2“ referenzieren, auf das Unternehmen „Test“ umgebogen werden, bevor „Test 2“ gelöscht werden kann. Wie mit den Duplikaten weiter Verfahren wird, sehe ich als eigenständiges Refactoring an. In diesem Szenario haben wir erfolgreich einen Unique-Constraint hinzugefügt und dabei die Daten gesichert, die nötig sind, um Duplikatsbereinigungen durchzuführen.

Szenario 3: Die Spalte ist nicht unique, die Bereinigung nicht automatisierbar

Bei Szenario 3 kann der Inhalt der Spalten, die unique gemacht werden, nun nicht mehr automatisch unique gemacht werden. Um Duplikate zu entfernen, müssen Benutzer manuelle Tätigkeiten ausführen. Dieses Refactoring ist also nicht ohne Koordination mit gewissen Benutzergruppen möglich. Folgendes vorgehen hilft dabei Probleme zu vermeiden.

  1. Zuerst muss die Applikation auf Duplikate prüfen, ohne das ein solcher Constraint bereits existiert.
    1. Validierungs-Fehler sollen dabei nur ausgelöst werden, wenn die entsprechenden Daten von einem Benutzer bearbeitet werden. Wenn z.B. für die JPA-Entity Company zwei Masken existieren, wobei in einer Maske der Name geändert werden kann und in der anderen andere Daten aber nicht der Name, so soll nur in der Maske ein Validierungs-Fehler angezeigt werden auf der der Name geändert werden kann. Falls also JSR 303 Bean Validation eingesetzt wird, so ist die Verwendung einer eigenen Validation-Group nötig, um dieses Verhalten zu implementieren.
    2. Mitunter kann es sinnvoll sein ein Feature Toggle zu benutzen um die Validierung erst nach dem Deployment der neuen Applikationsversion scharf zu stellen. Auch unterschiedliche Phasen sind vorstellbar z.B.
      1. NONE: keine Validierung
      2. FORBID-NEW-DUPLICATES: Ab jetzt dürfen zwar alte Datensätze noch doppelte Namen haben. Neue werden aber nicht mehr zugelassen.
      3. FORBID-ALL-DUPLICATES: Ab jetzt dürfen keine doppelten Namen mehr gespeichert werden.
  2. Diese Änderung muss in die Produktionsumgebung. Wir brauchen also eine „Transition Period“ zwischen der Änderung der Applikation und der Änderung der Datenbank.
  3. Sobald die Applikations-Änderung in der Produktion ist, muss dem Kunden Bescheid gegeben werden das die Duplikate entfernt werden sollen.
  4. Wenn der Feature-Toggle-Ansatz gewählt wurde, dann darf die Einstellung des Toggle keine Duplikate mehr erlauben.
  5. Datenbank-Constraint einfügen.

Im Buch „Refactoring Databases – Evolutionary Database Design“ wird noch ein Szenario beschrieben, bei dem es einfach nicht möglich ist einen Constraint einzufügen, weil immer wieder Duplikate eingefügt werden müssen. Die vorgeschlagene Lösung ist im Grunde kein Database-Refactoring mehr, sondern schlägt eine Strategie zur Behandlung dieses Problems vor . Man lässt ein Skript im Batch-Mode zu Zeiten niedriger Last laufen, welches „Constraint“-Verletzungen aufzeichnet. Für die Bereinigung der „Constraint“-Verletzungen kann die Applikation dann eine Benutzerschnittstelle zur Verfügung stellen.

Am Beispiel der Company-Tabelle könnte das wie folgt aussehen:

Create-Table für Duplikats-Tabelle

create table CompanyDuplicates ( 
duplicate_id bigint primary key, -- id des neu eingefügten duplikats
reference_id bigint not null, -- id des ersten Eintrags mit der ID. dupDetectionTime datetime not null,
)

Batch-Skript:

insert into CompanyDuplicates  
select x.id as duplicate_id
,(select MIN(id) from Company c where c.name = x.name group by c.name) as reference_id
,GETDATE()
from (
select id,name, ROW_NUMBER() over(PARTITION BY name ORDER BY ID) rownr from Company
) as x
where x.rownr > 1 and x.id not in (select duplicate_id from CompanyDuplicates)

Wie man sieht, kann das Hinzufügen eines Spalten-Constraints vom Aufwand her stark variieren. Die Szenarien 1-4 sind in Bezug auf diesen Aspekt aufsteigend angeführt. Probleme entstehen, wenn man das Szenario, in dem man sich befindet unterschätzt. Man also z.B. das Refactoring aus Szenario 2 anwendet, obwohl man sich in Szenario 4 befindet.

Datenbank-Refactoring ist ein komplexes Thema. Oft wird aus Angst etwas kaputt zu machen versucht ein Refactoring zu umgehen. Man zögert dadurch aber eine notwendige Änderung an einem bestehenden Schema oft nur hinaus, handelt sich damit mitunter spätere Performance- und Konsistenz-Probleme ein. Meiner Einschätzung nach führt das Refactoring eines problematischen Schemas zu einer Vereinfachung der Applikationen, die darauf basieren, und kann so die Produktivität ihrer Entwickler erhöhen.

Welche Erfahrungen hast du mit Database Refactoring gemacht? Konntest du bei anderen Refactorings ähnliche Spezialfälle identifizieren? Wir freuen uns auf deine Kommentare.

Christian Proinger
(Software Architect)