wake-up-neo.com

Kann ein Fremdschlüssel auf einen nicht eindeutigen Index verweisen?

Ich dachte, ein Fremdschlüssel bedeutete, dass eine einzelne Zeile auf eine einzelne Zeile verweisen muss, aber ich betrachte einige Tabellen, bei denen dies definitiv nicht der Fall ist. Tabelle1 hat Spalte1 mit einer Fremdschlüsseleinschränkung für Spalte2 in Tabelle2, ABER es gibt viele Datensätze in Tabelle2 mit demselben Wert in Spalte2. Es gibt auch einen nicht eindeutigen Index für column2. Was bedeutet das? Bedeutet eine Fremdschlüsselbedingung einfach, dass mindestens ein Datensatz mit den richtigen Werten in den rechten Spalten vorhanden sein muss? Ich dachte, es bedeutete, dass es genau eine solche Platte geben muss (nicht sicher, wie Nullen in das Bild passen, aber das macht mir im Moment weniger Sorgen).

update: Anscheinend ist dieses Verhalten spezifisch für MySQL, was ich verwendete, aber ich habe es in meiner ursprünglichen Frage nicht erwähnt.

37
allyourcode

Aus MySQL-Dokumentation :

InnoDB erlaubt es einer Fremdschlüsseleinschränkung, auf einen nicht eindeutigen Schlüssel zu verweisen. Dies ist eine InnoDB-Erweiterung für Standard-SQL.

Es gibt jedoch einen praktischen Grund, Fremdschlüssel für nicht eindeutige Spalten der referenzierten Tabelle zu vermeiden. Was sollte also die Bedeutung von "ON DELETE CASCADE" in diesem Fall sein?

Die Dokumentation empfiehlt weiter :

Die Behandlung von Fremdschlüsselverweisen auf nicht eindeutige Schlüssel oder Schlüssel, die NULL-Werte enthalten, ist nicht genau definiert (...). Es wird empfohlen, Fremdschlüssel zu verwenden, die nur auf UNIQUE (einschließlich PRIMARY) und NOT NULL-Schlüssel verweisen.

44
Hobbes

Ihre Analyse ist korrekt. Die Schlüssel müssen nicht eindeutig sein, und Einschränkungen wirken sich auf die Menge der übereinstimmenden Zeilen aus. Normalerweise kein nützliches Verhalten, aber es können Situationen auftreten, in denen Sie das wollen, was Sie wollen.

7
chaos

Ja, Sie können Fremdschlüssel für praktisch jede Spalte in einer beliebigen Tabelle erstellen. In den meisten Fällen erstellen Sie sie jedoch zum Primärschlüssel.

Wenn Sie Fremdschlüssel verwenden, die nicht auf einen Primärschlüssel verweisen, möchten Sie möglicherweise einen (nicht eindeutigen) Index für die Spalten erstellen, auf die aus Gründen der Leistung verwiesen wird.

Hängt von dem verwendeten RDBMS ab. Ich denke, einige tun dies implizit für Sie oder verwenden andere Tricks. RTM.

3
Evan

In diesem Fall bedeutet dies normalerweise, dass zwei Fremdschlüssel miteinander verknüpft werden. Oftmals ist die Tabelle, die den Schlüssel als Primärschlüssel enthalten würde, nicht einmal im Schema enthalten.

Beispiel: Zwei Tabellen, COLLEGES und STUDENTS, enthalten beide eine Spalte namens ZIPCODE.

Wenn wir kurz nachsehen 

SELECT * FROM COLLEGES JOIN STUDENTS ON COLLEGES.ZIPCODE = STUDENTS.ZIPCODE

Wir könnten feststellen, dass die Beziehung zu vielen sehr viel ist. Wenn unser Schema eine Tabelle namens ZIPCODES mit dem Primärschlüssel ZIPCODE hätte, wäre es offensichtlich, was wirklich vor sich geht. 

Unser Schema hat jedoch keine solche Tabelle. Nur weil unser Schema keine solche Tabelle hat, bedeutet dies nicht, dass solche Daten jedoch nicht existieren. Irgendwo im USPO-Land gibt es einen solchen Tisch. Und sowohl COLLEGES.ZIPCODE als auch STUDENTS.ZIPCODE sind Verweise auf diese Tabelle, auch wenn wir sie nicht anerkennen.

Dies hat mehr mit der Datenphilosophie zu tun als mit dem Erstellen von Datenbanken, aber es zeigt deutlich etwas Wesentliches: Die Daten haben Eigenschaften, die wir entdecken, und nicht nur Eigenschaften, die wir entdecken. Natürlich könnten wir entdecken, was jemand anderes erfunden hat. Dies ist sicherlich bei ZIPCODE der Fall.

3
Walter Mitty

PostgreSQL lehnt dies ebenfalls ab (trotzdem, auch wenn möglich , bedeutet dies nicht, dass es eine gute Idee ist):

essais=> CREATE TABLE Cities (name TEXT, country TEXT);
CREATE TABLE
essais=> INSERT INTO Cities VALUES ('Syracuse', 'USA');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Syracuse', 'Greece');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Paris', 'France');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Aramits', 'France');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Paris', 'USA');
INSERT 0 1

essais=> CREATE TABLE People (name TEXT, city TEXT REFERENCES Cities(name));
ERROR:  there is no unique constraint matching given keys for referenced table "cities"
1
bortzmeyer

Von welcher Datenbank sprechen wir? In SQL 2005 kann ich keine Fremdschlüsseleinschränkung erstellen, die auf eine Spalte verweist, die keine eindeutige Einschränkung hat (Primärschlüssel oder anderweitig).

create table t1
(
  id int identity,
  fk int
);

create table t2
(
  id int identity,
);

CREATE NONCLUSTERED INDEX [IX_t2] ON [t2] 
(
    [id] ASC
);
ALTER TABLE t1 with NOCHECK
ADD CONSTRAINT FK_t2 FOREIGN KEY (fk)
    REFERENCES t2 (id) ;


Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 't2' 
that match the referencing column list in the foreign key 'FK_t2'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Wenn Sie dies tatsächlich tun könnten, hätten Sie tatsächlich eine Viele-zu-Viele-Beziehung, die ohne eine Zwischentabelle nicht möglich ist. Ich wäre wirklich daran interessiert, mehr darüber zu erfahren ...

Siehe auch diese verwandte Frage und Antworten.

0
cdonner

Nekromanzierung. 
Wie bereits erwähnt, sollten Sie einen nicht eindeutigen Schlüssel nicht als Fremdschlüssel referenzieren. 
Aber was Sie stattdessen tun können (ohne Delete-Kaskadengefahr), ist das Hinzufügen einer Check-Einschränkung (zumindest in MS-SQL). 
Das ist nicht genau dasselbe wie ein Fremdschlüssel, aber zumindest wird das Einfügen ungültiger/verwaister/toter Daten verhindert.

Siehe hier als Referenz (Sie müssen den MS-SQL-Code in die MySQL-Syntax portieren):
Fremdschlüssel zu Nicht-Primärschlüssel

Bearbeiten:
Auf der Suche nach den Gründen für den Downvote, gemäß Mysql CHECK Constraint , unterstützt MySQL CHECK-Constraints nicht wirklich. 
Sie können sie aus Kompatibilitätsgründen in Ihrer DDL-Abfrage definieren, sie werden jedoch einfach ignoriert ...

Wie bereits erwähnt, können Sie jedoch einen BEFORE INSERT- und BEFORE UPDATE-Auslöser erstellen, der einen Fehler ausgibt, wenn die Anforderungen der Daten nicht erfüllt werden.

Zur Frage:

Ich dachte, ein Fremdschlüssel bedeutete, dass eine einzelne Zeile auf ein .__ verweisen muss. einzeilig, aber ich betrachte einige Tische, an denen dies definitiv .__ ist. nicht der Fall.

Dies ist in jedem normalen RDBMS der Fall. 
Die Tatsache, dass dies in MySQL möglich ist, ist nur ein weiterer Grund 
MySQL ist ein in-sane RDBMS.
Es mag zwar schnell sein, aber die Beeinträchtigung der referentiellen Integrität und der Datenqualität auf dem Altar der Geschwindigkeit ist nicht meine Vorstellung von einem Qualitäts-RDBMS. 
Wenn es nicht ACID-konform ist, ist es überhaupt kein (korrekt funktionierendes) RDBMS.

0
Stefan Steiger