wake-up-neo.com

Warum werden Fremdschlüssel in der Theorie häufiger verwendet als in der Praxis?

Wenn Sie relationale Theorie studieren, sind Fremdschlüssel natürlich obligatorisch. In der Praxis werden Tabellenprodukte und Joins an jedem Ort, an dem ich gearbeitet habe, immer durch explizite Angabe der Schlüssel in der Abfrage erstellt, anstatt sich auf Fremdschlüssel im DBMS zu verlassen.

Auf diese Weise können Sie natürlich zwei Tabellen durch Felder verbinden, die keine Fremdschlüssel sein sollen, was zu unerwarteten Ergebnissen führt.

Warum denkst Du, das ist? Sollten DBMSs nicht erzwingen, dass Joins und Produkte nur über Fremdschlüssel erstellt werden?

EDIT: Danke für alle Antworten. Mir ist jetzt klar, dass der Hauptgrund für FKs die Referenzintegrität ist. Wenn Sie jedoch einen DB entwerfen, werden alle Beziehungen im Modell (dh die Pfeile in der ERD) zu Fremdschlüsseln, zumindest theoretisch, unabhängig davon, ob Sie sie in Ihrem DBMS als solche definieren oder nicht, sie sind semantische FKs. Ich kann mir nicht vorstellen, dass Tabellen durch Felder verknüpft werden müssen, die keine FKs sind. Kann jemand ein Beispiel geben, das Sinn macht?

PS: Mir ist bewusst, dass N: M-Beziehungen zu separaten Tabellen und nicht zu Fremdschlüsseln werden. Der Einfachheit halber wurde sie einfach weggelassen.

48
Petruza

Der Grund für Fremdschlüsseleinschränkungen besteht darin, die Existenz der referenzierten Zeilen zu gewährleisten.

"Der Fremdschlüssel identifiziert eine Spalte oder einen Satz von Spalten in einer Tabelle, die auf eine Spalte oder einen Satz von Spalten in einer anderen Tabelle verweist. Die Werte in einer Zeile der referenzierenden Spalten müssen in einer einzigen Zeile in der referenzierten Tabelle stehen. 

Daher kann eine Zeile in der referenzierenden Tabelle keine Werte enthalten, die in der referenzierten Tabelle nicht vorhanden sind (außer möglicherweise NULL). Auf diese Weise können Referenzen hergestellt werden, um Informationen miteinander zu verknüpfen. Dies ist ein wesentlicher Bestandteil der Datenbanknormalisierung. "( Wikipedia )


RE: Deine Frage: "Ich kann mir nicht vorstellen, dass Tabellen nach Feldern zusammengefügt werden müssen, die keine FKs sind":

Bei der Definition einer Fremdschlüsseleinschränkung müssen die Spalten in der referenzierenden Tabelle der Primärschlüssel der referenzierten Tabelle sein oder mindestens ein Kandidatenschlüssel.

Bei Verknüpfungen müssen keine Primärschlüssel oder Kandidatenschlüssel verbunden werden.

Das folgende Beispiel kann sinnvoll sein:

CREATE TABLE clients (
    client_id       uniqueidentifier  NOT NULL,
    client_name     nvarchar(250)     NOT NULL,
    client_country  char(2)           NOT NULL
);

CREATE TABLE suppliers (
    supplier_id       uniqueidentifier  NOT NULL,
    supplier_name     nvarchar(250)     NOT NULL,
    supplier_country  char(2)           NOT NULL
);

Und dann Abfrage wie folgt:

SELECT 
    client_name, supplier_name, client_country 
FROM 
    clients 
INNER JOIN
    suppliers ON (clients.client_country = suppliers.supplier_country)
ORDER BY
    client_country;

Ein weiterer Fall, in dem diese Verknüpfungen sinnvoll sind, betrifft Datenbanken, die geografische Funktionen wie SQL Server 2008 oder Postgres mit PostGIS bieten. Sie können Abfragen wie diese machen:

SELECT
    state, electorate 
FROM 
    electorates 
INNER JOIN 
    postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1);

Quelle: ConceptDev - SQL Server 2008 Geografie: STIntersects, STArea

Ein anderes ähnliches Beispiel für ein Geospatial sehen Sie in der akzeptierten Antwort auf das Postproblem " Sql 2008" - welches LatLong in einem Geographiepolygon vorhanden ist? ":

SELECT 
    G.Name, COUNT(CL.Id)
FROM
    GeoShapes G
INNER JOIN 
    CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1
GROUP BY 
    G.Name;

Dies sind alles gültige SQL-Joins, die nichts mit Fremd- und Kandidatenschlüsseln zu tun haben und in der Praxis dennoch nützlich sein können.

39
Daniel Vassallo

Fremdschlüssel haben weniger mit Joins zu tun als mit der Beibehaltung der Datenbankintegrität. Der Beweis dafür ist, dass Sie Tabellen beliebig zusammenfügen können, auch wenn dies nicht unbedingt sinnvoll ist.

34
Otávio Décio

Ich kann mir nicht vorstellen, dass Tabellen durch Felder verbunden werden müssen, die keine FKs sind. Kann jemand ein Beispiel geben, das Sinn macht?

FOREIGN KEYs kann nur zum Erzwingen der referentiellen Integrität verwendet werden, wenn die Beziehung zwischen den Entitäten des ER-Modells mit einem Äquivalent zwischen zwei Beziehungen im relationalen Modell dargestellt wird.

Das trifft nicht immer zu.

Hier ist ein Beispiel aus dem Artikel in meinem Blog, den ich vor einiger Zeit geschrieben habe:

Dieses Modell beschreibt Waren- und Preisklassen:

Und hier ist die relationale Implementierung des Modells:

CREATE TABLE Goods (ID, Name, Price)
CREATE TABLE PriceRange (Price, Bonus)

Wie Sie sehen, hat die PriceRange-Tabelle nur ein preisbezogenes Attribut, Price, aber das Modell hat zwei Attribute: StartPrice und EndPrice.

Dies liegt daran, dass das relationale Modell die Umwandlung der Mengen ermöglicht und die Entität PriceRange mit SQL-Operationen leicht rekonstruiert werden kann.

Goods
ID  Name               Price
1   Wormy Apple        0.09
2   Bangkok durian     9.99
3   Densuke watermelon 999.99
4   White truffle      99999.99

PriceRange
Price   Bonus
0.01       1%
1.00       3%
100.00    10%
10000.00  30%

Wir speichern nur die Untergrenze jedes Bereichs. Die obere Grenze kann leicht abgeleitet werden.

Hier ist die Abfrage, um den Bonus für jedes Gut zu finden:

SELECT  *
FROM    Goods
JOIN    PriceRange
ON      PriceRange.Price =
        (
        SELECT  MAX(Price)
        FROM    PriceRange
        WHERE   PriceRange.Price <= Goods.Price
        )

Wir sehen, dass dieses relationale Modell das ER-Modell ziemlich gut implementiert, jedoch kann zwischen diesen Beziehungen kein Fremdschlüssel deklariert werden, da die zum Binden verwendete Operation kein Äquijoin ist.

10
Quassnoi

Nein, die Durchsetzung ist nicht notwendig. Dies würde einige nützliche Funktionen, wie etwa das mögliche Überladen von Spalten, nicht zulassen. Während diese Art der Verwendung nicht ideal ist, ist es IS in manchen realen Situationen nützlich.

Die geeignete Verwendung für Fremdschlüsseleinschränkungen ist genauso. eine Einschränkung für Werte, die zu einer bestimmten Spalte hinzugefügt werden, um sicherzustellen, dass ihre referenzierten Zeilen vorhanden sind.

Es ist zu beachten, dass ein erheblicher Mangel an Fremdschlüsseleinschränkungen für ein bestimmtes Schema ein schlechter "Geruch" ist und auf einige schwerwiegende Konstruktionsprobleme hinweisen kann.

8
Paul Sonier

Sie können jedem Ausdruck beitreten. Ob Sie Fremdschlüssel in Ihrer Datenbank definieren oder nicht, ist unerheblich. Fremdschlüssel beschränken INSERT/UPDATE/DELETE, nicht SELECT.

Warum lassen viele Projekte keine Fremdschlüssel definieren? Es gibt verschiedene Gründe:

  • Das Datenmodell ist schlecht ausgelegt und erfordert gebrochene Referenzen (Beispiele: polymorphe Assoziationen, EAV).

  • Möglicherweise haben die Programmierer gehört, dass "Fremdschlüssel langsam sind", also fallen sie. Durch die zusätzliche Arbeit, die Sie zur Gewährleistung der Datenkonsistenz leisten müssen, wenn Sie sich nicht auf Fremdschlüssel verlassen können, ist Ihre App weniger effizient. Vorzeitige Optimierung, ohne den Nutzen zu messen, ist ein weit verbreitetes Problem.

  • Einschränkungen stehen einigen Datenbereinigungsaufgaben im Weg. Manchmal müssen Sie die Referenzen vorübergehend unterbrechen, wenn Sie Daten umgestalten. In vielen RDBMS können Einschränkungen deaktiviert werden, aber manchmal entscheiden die Programmierer, dass es einfacher ist, zu lassen, sie deaktiviert zu lassen. Wenn häufig die Einschränkungen deaktiviert werden müssen, deutet dies wahrscheinlich auf einen stark beschädigten Datenbankentwurf hin.

6
Bill Karwin

Fremdschlüssel, die in der von Ihnen beschriebenen Weise verwendet werden, beziehen sich nicht auf deren Verwendung. Sie sollen sicherstellen, dass, wenn ein Datensatz logisch von einem entsprechenden Datensatz an anderer Stelle abhängt, der entsprechende Datensatz tatsächlich vorhanden ist.

Ich glaube, wenn Entwickler/dbas Zeit haben, entweder (A) gute Namen für ihre Tabellen und Felder zu entwickeln oder (B) umfangreiche Fremdschlüsseleinschränkungen zu definieren, ist OptionAdie einfache Wahl. Ich habe in beiden Situationen gearbeitet. Dort, wo umfangreiche Einschränkungen für die Aufrechterhaltung der Ordnung und die Verhinderung von Angriffen durch die Menschen vorausgesetzt wurden, kann das wirklich zum Chaos werden.

Es ist viel Aufwand erforderlich, um alle Ihre Fremdschlüsseleinschränkungen während der Entwicklung auf dem neuesten Stand zu halten. Zeit, die Sie möglicherweise für andere hochwertige Aufgaben verwenden, für die Sie kaum Zeit haben. Im Gegensatz dazu sind die Fremdschlüssel in Situationen, in denen Sie gute Namenskonventionen haben, sofort klar. Entwickler müssen nicht nach Fremdschlüsseln suchen oder eine Abfrage versuchen, um zu sehen, ob sie funktioniert. Sie können nur die Beziehungen sehen.

Ich denke, Fremdschlüsseleinschränkungen werden schnell hilfreich, wenn die Anzahl der verschiedenen Teams wächst, die eine Datenbank verwenden. Es wird schwierig, eine konsistente Namensgebung durchzusetzen. Kenntnis der DB wird fragmentiert; Es ist einfach, dass db actions unbeabsichtigte Folgen für ein anderes Team hat.

3
Patrick Karcher

Weil Theorie in der Praxis nicht ausreicht;)

Im Ernst, meine Erfahrung besteht hauptsächlich darin, dass die Theorie nicht flexibel genug ist, um alle Möglichkeiten zu berücksichtigen, mit denen Sie in der realen Welt umgehen müssen. Nur mit einem äußerst bizarren Fall, den Sie in Ihrer Datenbank speichern müssen (oder etwas üblicherem, wie das Überladen von Spalten), müssen Sie den FK herausholen und in der DAL implementieren. 

Möglicherweise können Sie eine Lösung entwickeln, die auf völlig normalisierte Weise (zum Beispiel) archiviert werden kann, aber in vielen Fällen sind die Arbeit und/oder das Endergebnis nicht ausreichend.

Meine zwei Cent.

3
j.a.estevan

DBMSs sind so ausgelegt, dass sie eine Vielzahl von Lösungen ermöglichen und gleichzeitig nach ihren Kernregeln arbeiten.

Das Einschränken von Joins auf definierte Fremdschlüssel würde die Funktionalität enorm einschränken, zumal die meisten Entwicklungen nicht mit einem dedizierten DBA oder einer Überprüfung von SQL/gespeicherten Prozeduren erfolgen.

Abhängig von Ihrer Datenzugriffsebene müssen Sie jedoch möglicherweise Fremdschlüssel konfigurieren, um die Funktionalität nutzen zu können. Zum Beispiel Linq to SQL.

2
Bravax

Fremdschlüssel werden nicht so oft verwendet, wie dies in der relationalen Theorie der Fall ist, da die Leute vom Typ DB/relationaler Art nicht viel Code schreiben oder gar die Tabellen entwerfen. Programmierer schreiben die Code-/Designtabellen oder haben großen Einfluss auf die Gestaltung der Tabellen.

2
ElGringoGrande

An welcher Art von Datenbankanwendungen arbeiten Sie? Bei der Theorie, die Sie häufig sehen, geht es um die Verwendung der Datenbank-Raw. In diesem Fall können Einschränkungen sehr nützlich sein. In der Praxis werden Datenbanken häufig als Back-End für größere Anwendungen verwendet. In vielen Fällen müssen diese Anwendungen Transaktionen selbst validieren, und es wäre unnötig, sie in der Datenbank zu wiederholen.

Betrachten Sie zum Beispiel eine Verkaufsanwendung. Wenn jemand eine Bestellung aufgibt, wird der Kunde möglicherweise in der Datenbank nach Adressen oder Kreditkartendaten gesucht. Wenn es keinen Kunden findet, wird es darauf programmiert, etwas Vernünftiges zu tun. Wenn er wartete, bis er versuchte, eine Zeile in die Auftragstabelle einzufügen, um festzustellen, dass es keinen Kunden gab, würde das Feedback langsamer und weniger bequem werden.

Etwas muss die Integrität der Datenbank aufrechterhalten, aber es ist nicht immer der beste Weg, dies innerhalb des DBMS selbst zu tun.

2
David Thornley

Ich programmiere schon seit einigen Jahrzehnten, lange bevor relationale Datenbanken zur Norm wurden. Als ich anfing, mit MySQL zu arbeiten, als ich mir PHP selbst beibrachte, sah ich die Option "Fremdschlüssel" und der allererste Gedanke war "Wow! Das ist zurückgeblieben". Der Grund, nur ein Dummkopf zu sein, glaubt, dass das Labor die Realität bestimmt. Es war sofort offensichtlich, dass Sie, wenn Sie nicht eine Anwendung programmieren, die niemals geändert werden würde (ever)), Ihre Anwendung in einem Stahlguss umhüllen, wobei die einzige Möglichkeit darin besteht, entweder mehr Tabellen zu erstellen oder kreative Lösungen zu finden .

Diese anfängliche Beurteilung war in jeder einzelnen realen Produktionsanwendung, die mir begegnet ist, geboren worden. Die Einschränkung verlangsamt nicht nur jede Änderung erheblich, sondern macht das Anwachsen der Anwendung fast unmöglich, was für ein Unternehmen erforderlich ist.

Der einzige Grund, den ich jemals für Einschränkungen in der Tabelle gefunden habe, sind faule Codierer. Nicht bereit, reinen Code zu schreiben, um die Datenintegrität zu überprüfen. 

Michael

2
Michael

Fremdschlüssel sind äußerst wichtig, insbesondere in Datenbanken, in denen manuelle Abfragen ausgeführt werden oder Software aktiv für sie entwickelt wird. Jede ungetestete Abfrage, die in der Datenbank ausgeführt wird, kann Fehler enthalten. Einschränkungen wie Fremdschlüssel dienen dazu, diese Fehler hervorzuheben, bevor Inkonsistenzen in die Daten eingefügt werden.

Diese Einschränkungen werden vom Designer des Schemas angewendet und stellen sicher, dass die Daten in dem Modell verbleiben, das er sich vorgestellt hat. Wenn die Einschränkungen nicht vorhanden sind, führt eine Abfrage früher oder später zu einer Inkonsistenz. Inkonsistenzen führen zu unvorhersehbaren Ergebnissen von Abfragen und lassen sich nur schwer rückgängig machen.

1
Paul

Ein Teil davon ist für mich, dass (und dies ist eine schlechte Ausrede) die Benutzeroberfläche in MS SQL Server Management Studio zum Hinzufügen von Fremdschlüsseln awful ist. 

Ein Fremdschlüssel ist eine Einschränkung, die besagt, dass "jeder Wert in Spalte x in Tabelle a in Spalte y in Tabelle b angezeigt werden muss". Die Benutzeroberfläche für die Angabe in SSMS gibt jedoch nicht eindeutig an, mit welcher Tabelle Sie sich befassen, und dies ist die übergeordnete Tabelle, die die untergeordnete Tabelle ist, und so weiter. 

Jedes Mal, wenn ich einen Fremdschlüssel erstellen musste, war es ein Versuch und Irrtum, bis es anscheinend funktionierte.

0
Aric TenEyck

Gute Frage. Ich habe mich immer gefragt, warum SQL keine ähnliche Syntax hat

SELECT tbl1.col1, tbl2.col2
  FROM tbl1
  JOIN tbl2 USING(FK_tbl1_tbl2)

dabei ist FK_tbl1_tbl2 eine Fremdschlüsseleinschränkung zwischen den Tabellen. Dies wäre unglaublich viel nützlicher als NATURAL JOIN oder Oracle USING (col1, col2).

0
erikkallen

Der Hauptgrund ist, dass es keine Möglichkeit gibt, sie ohne Abfrage in den meisten MySQL-GUI-Tools (Navicat, MySQL usw.) einzurichten.

Hört sich dumm an, aber ich bin auch deswegen schuld, da mir die Syntax nicht auswendig ist: /

0