✔ Referentielle Integrität zwischen Tabellen und DB-Entwurf (How to)

[WiZdooM]

Hallo

Ich bin gerade dabei einen neuen Datenbankentwurf zu machen, da die alte Datenbank mittlerweile derart inkonsistent ist und in 10. Generation administriert wird. Zur Zeit liegen etliche Leichen in der Datenbank, die nicht mehr zu den entsprechenden Kunden oder Fahrzeugen oder Händlern oder Außendienstbetreuern zugeordnet werden können.

Da die Benutzer über ein Frontend auf die Daten zugreifen werden (Datenein-/Ausgabe), ist mir hier besonders die referentielle Integrität der Daten wichtig, um entsprechend "Leichen" zu vermeiden.

Wie muss ich mir die arbeitsweise der referentiellen Integrität (also Cascade on delete/update) vorstellen ? Wie muss so ein Tabellenkonstrukt aussehen ? Wirkt die RI in beide Richtungen bzw in welcher Tabelle müssen die Daten geändert werden? Wie werden die Daten korrekt eingetragen um die RI zu gewährleisten? Wie verhält sich das wenn ein Primärschlüssel an mehrere Fremdschlüssel in unterschiedlichen Tabellen gekoppelt ist ? Welche Auswirkung hat ein "Delete Record" auf die Datensätze, die auf diesen (zu löschenden) Datensatz verweisen - verfallen die automatisch oder müssen die separat gelöscht werden ? In welcher Reihenfolge müssen diese dann gelöscht werden ?

Mir fallen bestimmt noch hunderttausend Fragen dazu ein, aber ich glaub das reicht erstmal für den Anfang zu diesem Thema.

Wie bereits oben erwähnt bin ich derzeit im Entwurf, also der Aufdröselung der bisherigen Daten und Gruppierung dieser in entsprechende Tabellen. Wie geht ihr bei sowas vor ? Was muss man beachten (außer das alle Tabellen vom gleichen Typ sind) ?
Ich habe bisher nur mit "unverknüpften" Tabellen gearbeitet. Unverknüpft deswegen, weil ich keine RI vom DBMS nutze sondern programmiertechnisch (insert/delete) meine Daten bearbeitet bzw. entsprechend die Recordsets einzeln per Hand gelöscht habe.

Wäre toll wenn ihr mir da Tips geben könnt.

 

[jeipack]

Hi
Vorne Weg: RIs sind super wenn sie laufen! (Bei mir ist leider InnoDB gecrasht und die RIs sind weg, nun musste ich ein Tool schreiben dass die Arbeit der RIs im Nachhinein macht - Leichen löschen und so)

Ich kann dir sagen wie es in MySQL läuft:
Gehen wir von 2 einfachen Tabellen aus:

tbl_master:
---------------
int id;
char text;


tbl_slave:
------------
int id;
char text;
int fs_master;

fs_master aus der slave Tabelle zeigt auf die id der master Tabelle. Also ganz normaler Fremdschlüsseleintrag. Nun löschen wir den Eintrag in der mastertabelle und schauen uns an was passieren würde wenn der Fremdschlüssel von folgendem Typ wäre:

CASCADE:
Sobald der MasterEintrag gelöscht wird werden alle SlaveEinträge die auf diesen MasterEintrag zeigten gelöscht.

SETNULL:
Sobald der MasterEintrag gelöscht wird werden bei SlaveEinträge die auf diesen MasterEintrag zeigten der Fremdschlüssel auf Null gesetzt.

NOACTION:
Der MasterEintrag kann nicht gelöscht werden solange es noch einen SalveEintrag gibt der auf diesen MasterEintrag zeigt.


Dann noch zu deinen Fragen:
> Wie werden die Daten korrekt eingetragen um die RI zu gewährleisten?
Zuerst muss der Primärschlüssel vorhanden sein befor der FS eingetragen werden kann. Oder aber du stellst den CheckKeys temporär fürs Einfügen von Daten ab - allerdings werden hier die RIs nicht gewährt.

> Wie verhält sich das wenn ein Primärschlüssel an mehrere Fremdschlüssel in unterschiedlichen Tabellen gekoppelt ist ?
Genau gleich wie bei einer Tabelle (Mach einfach eine tbl_slave2 und teste es)


Noch ein Tipp:
Nenne die Fremdschlüssel immer mit einem Präfix (z.B. fs) und dann den vollständigen Tabellennamen (ohne Tabellenpräfix, falls du so einen verwendest).


MySQL Infos zu Constraints:
http://dev.mysql.com/doc/refman/5.1/de/innodb-foreign-key-constraints.html


Gruss
jeipack

 

[WiZdooM]

Hallo Jeipack,

Danke vielmals für die Aufklärung, dann hab ich die Constraintsbeschreibung doch nicht ganz so falsch verstanden.

 

[SeeKing]

Jaein,

es kommt auf deine Datenbank an. Die einen können da mehr die anderen weniger. MySQL ist da etwas "stiefmütterlich". Ich will MySQL nicht schlecht machen aber hier wird viel "Verantwortung" speziell wegen den "Leichen" auf den Entwickler abgewächlst.

Oracle, DB2 und Postgresql sind hier wesentlich restriktiver aber auch zum Teil stabiler. Je nachdem welche Datenbank du wählst sieht der physikal Layer für deinen Datenbankentwurf marginal anderst aus.

 

[WiZdooM]

Natürlich. Sowas ist im Detail immer auch abhängig vom Datenbanksystem selbst.