Alle Tabellen zu einem Index und dem zugehörigen Foreign Key?

H

hmeyer

Benutzer
Beiträge
8
Hallo zusammen,

ich bin mit Datenbankabfragen in Oracle leider nicht so firm. Ich suche jetzt nach einer Möglichkeit anhand eines Indexfeldes einer Tabelle abzufragen welche anderen Tabellen über eine Foreign Key verbunden sind. Es geht mir halt darum, dass wenn sich etwas in dieser Tabelle ändert, welche Auswirkungen hat das auf die verbundenen Tabellen. Ich habe eine Tabelle mit doppelten Einträgen. Wenn ich diese Einträge aber einfach lösche, dann hängt wohlmöglich der Foreign Key einer anderen Tabelle in der Luft. Danke für hilfreiche Tipps.

Viele Grüße
Horst
 
Werbung:
Also Indexe haben damit erstmal nichts zu tun, die nutzt das DBMS automatisch, falls vorhanden, und pflegt sie i.d.R. auch selbst.

Setzt man in SQL einen Foreign Key ist das eigentlich ein Foreign Key Constraint. Wird der Constraint verletzt gibt es eine Fehlermeldung und die Operation kann nicht durchgeführt werden. Das bedeutet im Umkehrschluss: Gibt es einen Foreign Key in der DB kannst du ihn nicht verletzten. Beim Versuch wird er dir auch sagen welcher Constraint ein Löschen verhindert. Den kann man sich dann angucken und auf welche Tabelle er zeigt.

Natürlich gibt es Ausnahmen:
- Viele Anwendungsentwickler ignorieren leider die Möglichkeiten die ein DBMS bietet und meinen sie können es besser. Das heißt eine Tabelle hat zwar eine Spalte die als Fremdschlüssel fungiert aber das weiß nur die Anwendung, im DBMS ist nichts eingerichtet. Die Anwendung stellt damit die Datenintegrität sicher, nicht das DBMS. Es gibt also leider sehr oft Schlüssel, die das DBMS nicht kennt, sondern wirklich nur die Anwendung, die Entwickler und vielleicht noch die Doku. Da muss man sich dann mit gesundem Menschenverstand ran machen oder es lassen, die Datenbank kann es einem leider nicht sagen :-(
- Es gibt Funktionen wie ON DELETE CASCADE, wo verbundene Datensätze dann mit gelöscht werden. Das habe ich in der Praxis aber noch nie gesehen, würde in der Tabellendefinition stehen.

Ich kenne Oracle nicht aber i.d.R. kann man sich zu Tabellen und Schlüsselobjekten SQL Code anzeigen lassen. Wenn es FKs oder ON DELETE CASCADE gibt findest du es.
 
Die Informationen welche Tabellen andere Tabelle referenzieren oder referenziert werden, stehen in den Views USER_CONSTRAINTS und USER_CONS_COLUMNS da brauchst Du self joins auf die Views um von der "Ausgangstabelle" zu den "referenzierten Tabellen" zu kommen.

Um zu ermitteln welche Tabellen eine bestimmte Tabelle referenzieren, dann kann man das z.B. so machen

Code: 
select pc.constraint_name, 
       pc.table_name,
       fc.table_name as referenced_table_name
from user_constraints pc
  join user_constraints fc on pc.constraint_name = fc.r_constraint_name
where pc.constraint_type = 'P'
  and pc.table_name = 'MAIN_TABLE' -- hier ändern

Wenn Du die Spalten dazu haben willst, kann man das über USER_CONS_COLUMNS machen:

Code: 
select pc.constraint_name, 
       pc.table_name,
       (select listagg(pcc.column_name, ',') within group (order by pcc.position)
        from USER_CONS_COLUMNS pcc
        where pcc.constraint_name = pc.constraint_name) as constraint_columns,
       fc.table_name as referenced_table_name,
       (select listagg(fcc.column_name, ',') within group (order by fcc.position)
        from user_cons_columns fcc
        where fcc.constraint_name = fc.constraint_name) as referencing_columns
from user_constraints pc
  join user_constraints fc on pc.constraint_name = fc.r_constraint_name
where pc.constraint_type = 'P'
  and pc.table_name = 'MAIN'

Wenn man die JOIN Richtung umdreht, kann man die andere Richtung der Abhängigkeiten rauskriegen.

Aber eigentlich können alle SQL GUI Tools so was anzeigen, das ist sicherlich einfacher als sich mit den Oracle System-Views rumzuschlagen.
 
Castorp hat auch schon geantwortet, also tw redundant:

Was Du mit Index bezeichnest ist in der DB Welt etwas anderes, als Du meinst. Ein Index ist ein DB internes Object zur Beschleunigung der Suche Wert(en) in Spalte(n).

Ich vermute, Du möchtest wissen, welche FK auf den Datensatz zeigen, der zu Löschen wäre oder
Welcher der beiden doppelten DS gelöscht werden kann, da kein FK auf ihn zeigt?

Eine wichtige Frage dazu:
Sind die FK tatsächlich als Constraint in der DB eingetragen oder handelt es sich dabei nur um "gedachte" (oder obfuskierte) Bezüge?

Ja (nur gedacht):
Du musst die Bezüge kennen, rausfinden, testen... :|

Nein (es sind echte FK Constraints):
Du kannst die FK Beziehungen aus den Oracle Dictionary Views abfragen. Dann kennst Du sie.

Ansonsten gilt sowieso, was @ukulele schreibt: Sind FK definiert, würde das Löschen der Records mit einer Constraint Verletzung fehlschlagen (alles gut, nichts gelöscht), sind On Delete Cascade Constraints definiert, dann wäre alles weg. Also schlecht.

Auch on delete cascade kannst Du in den Dictionary Views abfragen.

Bleibt als letztes die Prüfung von Triggern, die dazwischen funken könnten.

Insgesamt unabhängig von all dem könntest Du ein Select bauen, das nur verwaiste Datensätze liefert und die dann gefahrlos löschen(vorausgesetzt, alle Bezüge sind bekannt -falls nicht definiert)
 
Stimmt, Trigger habe ich vergessen. Allerdings ist die Wahrscheinlichkeit hoch das man, wenn man Trigger im DBMS einsetzt, man auch Constraints setzt, wohl recht gut :)
 
Hallo zusammen,
ich denke der Ansatz von castorp geht in die richtige Richtung. Aber anscheinend habe ich mich unverständlich ausgedrückt, daher möchte ich es an einem Beispiel verdeutlichen:

Es gibt eine TableA mit doppelten Einträgen:
ID,Bez
1,eins
2,zwei
3,drei
4,eins
5,zwei
6,drei

Nun gibt es eine TableB die auf die TableA mit einem Foreign Key im Feld IDaufA auf das Indexfeld ID in der TableA verknüpft ist. Wenn ich jetzt die Einträge 4,5,6 löschen würde, würde Datensätze in der TableB auch gelöscht. Das möchte ich aber nicht, sondern es sollen alle Einträge mit 4,5,6 in 1,2,3 upgedatet werden. Und dazu suche ich alle Tabellen wir die TableB die mit einem Foreign Key auf die TableA verknüpft sind, damit ich die Datensätze anpassen kann. Und wenn alle Datensätze korrigiert sind, erst dann kann ich problemlos die Datensätze 4,5,6 in der TableA löschen ohne zu riskieren, dass verknüpfte Datensätze mitgelöscht werden. Ich hoffe ich habe mich verständlich ausgedrückt.

Gruß
Horst
 
Mit Indexfeld meinst du Schlüssel(-feld,-spalte,etc.), Index ist der falsche Begriff (sry klingt pingelig, ist aber was ganz anderes).

Zu deinem Beispiel:
Wenn du in Tabelle A einen Datensatz löscht und es gibt einen Foreign Key in Tabelle B der auf A zeigt dann wird der ganze Löschvorgang fehl schlagen. Voraussetzung für das Fehlschlagen ist a) es gibt einen in der Datenbank definierten Foreign Key und b) es gibt kein DELETE ON CASCADE (sehr unwahrscheinlich).

Wenn es einen in der Datenbank definierten Foreign Key gibt kannst du das heraus finden, nur darauf zielt castorp Post ab. Nicht wie du verknüpfte Schlüssel änderst sondern wie du sie erstmal überhaupt findest. Für dein Beispiel führe doch erstmal den Code aus:
Code: 
select pc.constraint_name,
       pc.table_name,
       (select listagg(pcc.column_name, ',') within group (order by pcc.position)
        from USER_CONS_COLUMNS pcc
        where pcc.constraint_name = pc.constraint_name) as constraint_columns,
       fc.table_name as referenced_table_name,
       (select listagg(fcc.column_name, ',') within group (order by fcc.position)
        from user_cons_columns fcc
        where fcc.constraint_name = fc.constraint_name) as referencing_columns
from user_constraints pc
  join user_constraints fc on pc.constraint_name = fc.r_constraint_name
where pc.constraint_type = 'P'
  and pc.table_name = '<hier TableB entragen>'
Der Select zeigt dir alle Schlüssel die für Tabelle B definiert wurden und wo sie hin zeigen.
 
Ergänzend:
Das ist einfach anders rum gedacht, es geht erstmal nur darum ist der Foreign Key in der DB definiert oder nicht. Ist er definiert kannst du ihn finden und dann ein Statement basteln das genau die Änderungen vollzieht die du dir wünscht.
 
Danke für die Hilfe. Ich hatte aber eher an soetwas gedacht:


Code: 
select TABLE_NAME, CONSTRAINT_NAME, 'MainTable' R_TABLE_NAME,R_CONSTRAINT_NAME from user_constraints where constraint_type='R' and
r_constraint_name=(select constraint_name from user_constraints where table_name='MainTable' and constraint_type='P')

Das hat mir jetzt weitergeholfen. Damit habe ich auch festgestellt, dass einige Foreign Keys gar nicht angelegt wurden, da ungültige Schlüssel in der Tabelle waren.
 
hmeyer schrieb:
Das hat mir jetzt weitergeholfen. Damit habe ich auch festgestellt, dass einige Foreign Keys gar nicht angelegt wurden, da ungültige Schlüssel in der Tabelle waren.
Zum Vergrößern anklicken....
Das ist genau das Problem. Die wenigsten Anwendungen kommen mit einer ordentlich aufgebauten Datenbank daher, die meisten bilden Logik zur Datenintegrität (wie z.B. Foreign Key Constraints) in der Anwendung ab, nicht in der DB. Offensichtlich ist hier die Anwendung auch für Inkonsistenzen verantwortlich, weil genau das was du vermeiden willst schon passiert ist (verursacht durch die Anwendung).

Wenn du jetzt selbst tätig werden willst kannst du eigentlich nur raten, welche Schlüssel zusammen gehören. Das ist mühsam, kann aber funktionieren.
 
Werbung:
hmeyer schrieb:
festgestellt, dass einige Foreign Keys gar nicht angelegt wurden, da ungültige Schlüssel in der Tabelle waren.
Zum Vergrößern anklicken....
dazu und zu @ukulele als Ergänzung:
Das Anlegen der FK muss einen Fehler werfen, wenn es fehlschlägt. Genau wie das Verletzten eines angelegten FK oder anderer Constraints.
Dieser Fehler darf natürlich nicht verschluckt werden, egal ob im Script oder Anwendung das machen.

Du solltest also rausfinden, wo das geschieht.

Außerdem, Constraints können auch benannt, disabled/enabled oder deferred sein.

Ein (brauchbar) benannter Constraint liefert schon aus der Fehlermeldung einen wertvollen Hinweis, worauf er sich bezieht. Im Gegensatz dazu sind Constraints oft automatisch benannt (durch DB), weil im DDL idR lediglich die Regel oder der Typ angegeben werden.

Ob ein Constraint deferred ist, disabled oder enabled kannst Du ebenfalls im Dictionary ablesen. Und wenn Du gerade an diesem Problem arbeitetest, lohnt sich anhand der Sachlage vermutlich auch ein Blick auf den Rest der Tabellen / Constraints.

Nur auf die Existenz des Constraints im Dictionary zu schauen, ist also trügerisch. Besonders, wenn man fertige Statements nimmt, die die notwendigen Informationen (disabled, ..) nicht anzeigen.
 
Zurück
Oben