• Sie befinden sich hier: Startseite > SQL-Backstube > Datenqualität mit Fremdschlüsseln sicherstellen

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

22.08.2010: Datenqualität mit Fremdschlüsseln sicherstellen

Problemstellung

Häufig wird bei Geschwindigkeitsproblemen auf die MySQL-Engine MyISAM verwiesen. Diese sei deutlich schneller als die InnoDB Variante. Wenn nur Performance wichtig ist, mag dies stimmen, aber wenn das Kriterium Datenqualität dazu kommt, dann ist MyISAM nur noch zweite Wahl.

Getestet mit folgenden Datenbanken:

* MySQL 5.1.30

Datenbank-Tabellen

Die Datenbank-Tabellen hierfür sehen wie folgt aus:

Listing 1:

1. /* Engine=MyISAM */
2. CREATE TABLE personal (
3. id INT NOT NULL,
5. PRIMARY KEY (id)
6. ) ENGINE=MyISAM;
8. CREATE TABLE organigramm (
9. id INT NOT NULL,
10. pers_id INT NOT NULL,
11. chef_id INT,
13. PRIMARY KEY (id),
15. CONSTRAINT fk_organigramm_01
16. FOREIGN KEY (pers_id)
17. REFERENCES personal (id)
18. ON DELETE CASCADE, -- ebenfalls löschen
20. CONSTRAINT fk_organigramm_02
21. FOREIGN KEY (chef_id)
22. REFERENCES personal (id)
23. ON DELETE SET NULL -- auf NULL setzen
24. ) ENGINE=MyISAM;

Testdaten

Nun noch die Testdaten für die nachfolgenden SQL-Abfragen.

Listing 2:

1. /**
2. * Personal
3. */
4. INSERT INTO personal VALUES
5. ( 1, 'Chef-1'),
6. ( 2, 'Abteilungsleiter-1' ),
7. ( 3, 'Abteilungsleiter-2' ),
8. ( 4, 'Abteilungsleiter-3' ),
9. ( 5, 'Angestellter-1' ),
10. ( 6, 'Angestellter-2' ),
11. ( 7, 'Angestellter-3' ),
12. ( 8, 'Angestellter-4' ),
13. ( 9, 'Angestellter-5' ),
14. (10, 'Angestellter-6' ),
15. (11, 'Angestellter-7' );
17. /**
18. * Organigramm: Person hat Vorgesetzten
19. */
20. INSERT INTO organigramm VALUES
21. ( 1, 1, NULL ),
22. ( 2, 2, 1 ),
23. ( 3, 3, 1 ),
24. ( 4, 4, 1 ),
25. ( 5, 5, 2 ),
26. ( 6, 6, 2 ),
27. ( 7, 7, 3 ),
28. ( 8, 8, 3 ),
29. ( 9, 9, 4 ),
30. (10, 10, 4 ),
31. (11, 11, 4 );

Das Organigramm

Der Inhalt der Tabelle organigramm sieht jetzt so aus. Der einzige, der keinen Vorgesetzten hat, ist der Chef. Dies wird dann mit einem '-' dargestellt

Listing 3:

1. /**
2. * Organigramm
3. */
4. SELECT p1.name AS person,
5. COALESCE(p2.name,'-') AS hat_vorgesetzten
6. FROM organigramm o1
7. JOIN personal p1
8. ON p1.id = o1.pers_id
9. LEFT JOIN personal p2
10. ON p2.id = o1.chef_id
11. ORDER BY o1.pers_id;
13. +--------------------+--------------------+
14. | person | hat_vorgesetzten |
15. +--------------------+--------------------+
16. | Chef-1 | - |
17. | Abteilungsleiter-1 | Chef-1 |
18. | Abteilungsleiter-2 | Chef-1 |
19. | Abteilungsleiter-3 | Chef-1 |
20. | Angestellter-1 | Abteilungsleiter-1 |
21. | Angestellter-2 | Abteilungsleiter-1 |
22. | Angestellter-3 | Abteilungsleiter-2 |
23. | Angestellter-4 | Abteilungsleiter-2 |
24. | Angestellter-5 | Abteilungsleiter-3 |
25. | Angestellter-6 | Abteilungsleiter-3 |
26. | Angestellter-7 | Abteilungsleiter-3 |
27. +--------------------+--------------------+
28. 11 rows in set (0.01 sec)

Die Kündigung!

Gesetzt den Fall, einem Abteilungsleiter wird gekündigt, was passiert mit dem Organigramm?

Listing 4:

1. /**
2. * Abteilungsleiter-2 wird entlassen (MyISAM)
3. */
4. DELETE FROM personal
5. WHERE id = 3;
7. Query OK, 1 row affected (0.01 sec)

Organigramm II

Nach dem Löschen werfen wir einen Blick auf das Organigramm. Das Ergebnis stellt sich eigentlich wie erwartet dar. Also alles paletti, Aufgabe gelöst?

Listing 5:

1. /**
2. * Organigramm II
3. */
4. SELECT p1.name AS person,
5. COALESCE(p2.name,'-') AS hat_vorgesetzten
6. FROM organigramm o1
7. JOIN personal p1
8. ON p1.id = o1.pers_id
9. LEFT JOIN personal p2
10. ON p2.id = o1.chef_id
11. ORDER BY o1.pers_id;
13. +--------------------+--------------------+
14. | person | hat_vorgesetzten |
15. +--------------------+--------------------+
16. | Chef-1 | - |
17. | Abteilungsleiter-1 | Chef-1 |
18. | Abteilungsleiter-3 | Chef-1 | (<= Der Abteilungleiter-2 ist verschwunden)
19. | Angestellter-1 | Abteilungsleiter-1 |
20. | Angestellter-2 | Abteilungsleiter-1 |
21. | Angestellter-3 | - | <= kein direkter Vorgesetzter zugeordnet
22. | Angestellter-4 | - | <= kein direkter Vorgesetzter zugeordnet
23. | Angestellter-5 | Abteilungsleiter-3 |
24. | Angestellter-6 | Abteilungsleiter-3 |
25. | Angestellter-7 | Abteilungsleiter-3 |
26. +--------------------+--------------------+
27. 10 rows in set (0.00 sec)

Probleme

Aber "unter der Haube" sieht das Ganze nicht mehr so gut aus. Denn nur aufgrund der LEFT JOIN Funktionalität hat das Ergebnis der SQL-Ausgabe (s. Listing: 5) eher zufällig gestimmt.

Listing 6:

1. /**
2. * Inhalt der Tabelle organigramm
3. */
4. SELECT o1.pers_id AS person,
5. o1.chef_id AS hat_vorgesetzten
6. FROM organigramm o1
7. ORDER BY o1.pers_id;
9. +--------+------------------+
10. | person | hat_vorgesetzten |
11. +--------+------------------+
12. | 1 | NULL |
13. | 2 | 1 |
14. | 3 | 1 | <= Datenmüll (bereits gelöschte ID)
15. | 4 | 1 |
16. | 5 | 2 |
17. | 6 | 2 |
18. | 7 | 3 | <= Datenmüll (bereits gelöschte ID)
19. | 8 | 3 | <= Datenmüll (bereits gelöschte ID)
20. | 9 | 4 |
21. | 10 | 4 |
22. | 11 | 4 |
23. +--------+------------------+
24. 11 rows in set (0.00 sec)

zusätzliche Nacharbeiten

Diese zusätzlichen SQL-Befehle sind bei der MyISAM Variante notwendig, damit die abhängigte Tabelle organigramm keine ungültigen Daten mehr enthält.

Listing 7:

1. /**
2. * Aufräumarbeiten
3. */
4. DELETE FROM organigramm
5. WHERE pers_id = 3;
7. UPDATE organigramm
8. SET chef_id = NULL
9. WHERE chef_id = 3;

Um sich diesen zusätzlichen Aufwand zu sparen, hatten wir eingangs (siehe Listing: 1) in der Tabelle organigramm folgende Regeln (Constraints) definiert, die aber von der MyISAM Engine nicht berücksichtigt werden. Was sollten diese eigentlich bewirken?

ON DELETE CASCADE bedeutet:
Beim Löschen der Elterntabelle (hier Personal), werden automatisch die abhängigen Datensätze in der Tabelle organigramm gelöscht.

ON DELETE SET NULL bedeutet:
Beim Löschen der Elterntabelle (hier Personal), werden automatisch die referenzierenden Spalten (chef_id) der Tabelle organigramm auf den Wert NULL gesetzt.

Umstieg auf InnoDB

Entfernen wir die beiden Tabellen noch einmal und legen sie neu an. Diesmal weisen wir als Engine "InnoDB" zu.

Listing 8:

1. /**
2. * Tabellen entfernen
3. */
4. DROP TABLE organigramm;
5. DROP TABLE personal;

Listing 8:

1. /* Engine=InnoDB */
2. CREATE TABLE personal (
3. id INT NOT NULL,
5. PRIMARY KEY (id)
6. ) ENGINE=InnoDB;
8. CREATE TABLE organigramm (
9. id INT NOT NULL,
10. pers_id INT NOT NULL,
11. chef_id INT,
13. PRIMARY KEY (id),
15. CONSTRAINT fk_organigramm_01
16. FOREIGN KEY (pers_id)
17. REFERENCES personal (id)
18. ON DELETE CASCADE, -- ebenfalls löschen
20. CONSTRAINT fk_organigramm_02
21. FOREIGN KEY (chef_id)
22. REFERENCES personal (id)
23. ON DELETE SET NULL -- auf NULL setzen
24. ) ENGINE=InnoDB;

Jetzt fügen wird nochmals die Daten ein (siehe Listing: 2). Damit haben wir wieder den gleichen Datenbestand wie vor unserem "Löschtest" (siehe Listing: 4).

Listing 9:

1. /**
2. * Abteilungsleiter-2 wird entlassen (InnoDB)
3. */
4. DELETE FROM personal
5. WHERE id = 3;
7. Query OK, 1 row affected (0.01 sec)

Listing 10:

1. /**
2. * Inhalt der Tabelle organigramm
3. */
4. SELECT o1.pers_id AS person,
5. o1.chef_id AS hat_vorgesetzten
6. FROM organigramm o1
7. ORDER BY o1.pers_id;
9. +--------+------------------+
10. | person | hat_vorgesetzten |
11. +--------+------------------+
12. | 1 | NULL |
13. | 2 | 1 | <= der Datensatz zur Person 3 ist nun weg
14. | 4 | 1 |
15. | 5 | 2 |
16. | 6 | 2 |
17. | 7 | NULL | <= der Vorgesetzte ist autom. auf NULL gesetzt
18. | 8 | NULL | <= der Vorgesetzte ist autom. auf NULL gesetzt
19. | 9 | 4 |
20. | 10 | 4 |
21. | 11 | 4 |
22. +--------+------------------+
23. 11 rows in set (0.00 sec)

Das Ergebnis und auch die Datenbasis ist nun wirklich korrekt und es gibt "keine Datenleichen" mehr im Keller.

Resumee

Mit Hilfe der Fremdschlüsselbeziehungen (Foreign-Key-Constraints) ermöglichst es die MySQL-Engine InnoDB deutlich einfacher Datenqualität zu garantieren. Ob der damit eingehandelte Geschwindigkeitsnachteil gegenüber MyISAM durch die deutlich bessere Datenqualität nicht ausglichen wird, hängt vom Projekt und den jeweiligen Anforderungen ab.

https://twiedmann.de