Tabellenstruktur/Konzeptfrage

[Vinzenz Mai]

Hallo,

» »» Mh, das versteh ich nicht ganz. Eine der Spalten als index nehmen?
» Nein, einen eindeutigen Index über die Kombination der beiden Fremdschlüsselspalten, gern auch als Primärschlüssel.

Was ist das und wie geht das?

Ein eindeutiger Index, der sich auf die Kombination zweier Spalten bezieht. Das heißt, in jeder Spalte kann jeder Wert durchaus mehrfach vorkommen, jede Kombination jedoch maximal einmal.

Und hier ein kleines Beispiel:

-- Tabelle maerkte  
CREATE TABLE maerkte (  
    id_markt INTEGER unsigned NOT NULL AUTO_INCREMENT,  
    markt    VARCHAR(45) NOT NULL,  
    PRIMARY KEY (id_markt) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
  
-- Tabelle produkte  
CREATE TABLE  produkte (  
    id_produkt  INTEGER unsigned NOT NULL AUTO_INCREMENT,  
    bezeichnung VARCHAR(45) NOT NULL,  
    PRIMARY KEY (id_produkt) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
  
-- Beziehungstabelle produkte_zu_maerkte  
CREATE TABLE  produkte_zu_maerkte (  
    id_markt   INTEGER unsigned NOT NULL, -- beachte: gleicher Datentyp  
                                          --          wie in Tabelle maerkte  
    id_produkt INTEGER unsigned NOT NULL, --          gleiches hier wie in  
                                          --          Tabelle produkte  
    PRIMARY KEY (id_markt, id_produkt),   -- unser Zweispalten-Index, hier als  
                                          -- Primärschlüssel  
    -- der erste Fremdschlüssel schränkt die Spalte id_produkt ein  
    KEY FK_produkte (id_produkt),  
    CONSTRAINT FK_produkte FOREIGN KEY (id_produkt)  
    REFERENCES produkte (id_produkt)      -- bezieht sich auf die Spalte  
                                          -- id_produkt der Tabelle produkte  
    ON DELETE CASCADE                     -- mit Löschweitergabe und  
    ON UPDATE CASCADE,                    -- Updateweitergabe  
  
    -- analog der zweite Fremdschlüssel, der die Beziehung zur Tabelle  
    -- maerkte herstellt. Gleiche Spaltennamen sind übrigens nicht erforderlich.  
    CONSTRAINT FK_maerkte FOREIGN KEY (id_markt)  
    REFERENCES maerkte (id_markt)  
    ON DELETE CASCADE  
    ON UPDATE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Fügen wir nun ein paar Datensätze ein:

INSERT INTO maerkte (id_markt, markt) VALUES  
(1, 'Matzes Delikatessen'),  
(2, 'Feinkost Schmieder');  

führt zu folgender Tabelle maerkte

id_markt | markt
---------+------------------------
      1  | Matzes Delikatessen
      2  | Feinkost Schmieder

und

INSERT INTO produkte (id_produkt, bezeichnung) VALUES  
(1, 'Milch'),  
(2, 'Honig'),  
(3, 'Sonnencreme'),  
(4, 'Wintermantel');

produkte

id_produkt | bezeichnung
-----------+--------------
         1 | Milch
         2 | Honig
         3 | Nektar
         4 | Ambrosia

Jetzt können wir die Märkte mit Produkten füllen:

Matzes Delikatessen führt Milch und Honig

INSERT INTO produkte_zu_maerkte (id_markt, id_produkt) VALUES  
(1, 1),  
(1, 2);  
-- wird erfolgreich ausgeführt  

Toms Feinkostladen fängt mit Nektar und Ambrosia an:

INSERT INTO produkte_zu_maerkte (id_markt, id_produkt) VALUES  
(2, 3),  
(2, 4);  
-- wird erfolgreich ausgeführt  

Tom nimmt Milch ins Programm:
Toms Feinkostladen fängt mit Nektar und Ambrosia an:

INSERT INTO produkte_zu_maerkte (id_markt, id_produkt) VALUES  
(2, 1);  
-- wird erfolgreich ausgeführt, diese Kombination gibt es noch nicht  

Schauen wir uns die Zuordnungstabelle an:

id_markt | id_produkt
---------+-----------
       1 | 1
       1 | 2
       2 | 3
       2 | 4
       2 | 1

Wir versuchen, die Milch erneut Matzes Delikatessenladen anzudrehen:

INSERT INTO produkte_zu_maerkte (id_markt, id_produkt) VALUES  
(1, 1);  
-- führt zu folgender Fehlermeldung  
-- Fehlernummer 1062, Duplicate entry '1-1' for key 'PRIMARY'  

Milch soll nun die ID 5 bekommen:

  
UPDATE  
    produkte  
SET  
    id_produkt = 5  
WHERE  
    bezeichnung = 'Milch'

Schauen wir uns nun die Beziehungstabelle an:

SELECT  
    id_markt,  
    id_produkt  
FROM  
    produkte_zu_maerkte  

Ergebnis:
id_markt | id_produkt
---------+-----------
       1 | 5
       1 | 2
       2 | 3
       2 | 4
       2 | 5

Wir sehen: ON UPDATE CASCADE sorgt dafür, dass in der Beziehungstabelle die id der Milch ebenfalls aktualisiert wird.

Nektar wird von der Produktliste gestrichen:

DELETE FROM  
    produkte  
WHERE  
    bezeichnung = 'Nektar'

Schauen wir uns erneut die Beziehungstabelle an:

Ergebnis:
id_markt | id_produkt
---------+-----------
       1 | 5
       1 | 2
       2 | 4
       2 | 5

Die Zuordnung von Nektar zu Feinkost Schmieder ist ebenfalls gelöscht worden - wegen ON DELETE CASCADE

Versuchen wir einen Artikel mit der id_produkt 7 (zu der es keinen Datensatz in der Tabelle produkte gibt) zu Matzes Laden hinzuzufügen:

  
INSERT INTO  
    produkte_zu_maerkte (id_markt, id_produkt)  
VALUES  
    (1, 7)

Dieses Statement schlägt fehl:
Fehlernummer 1452,
Cannot add or update a child row: a foreign key constraint fails ...

Ich hoffe, Du verstehst jetzt wie man FOREIGN-KEY-Constraints erstellen kann, und welchen Nutzen Du daraus ziehen kannst. Du solltest außerdem nachvollziehen können, wie man Mehrspaltenindexe erstellt und wie sie arbeiten. Eine Anmerkung noch: Es gibt auch nicht eindeutige Mehrspaltenindexe.

Freundliche Grüße

Vinzenz