Nutný index ve spojovací tabulce

Ivan M.:
Není to nutné, ale je to vhodné. Pamatuj na to, že primární klíč nemusí být jediný sloupec, může to být i kombinace existujících sloupců.
Asi bych v tomto případě napsal něco jako:
Raději smazáno, viz [#7] a [#8].

Každopádně nějaký klíč (nebo unikátní index) je vhodný, aby zaručil, že jedna fotka bude u jednoho zboží maximálně jednou.

Většinou se nový sloupeček s názvem id přidává tehdy, když aplikace neumí pracovat s vícesloupcovým primárním klíčem nebo pokud samotné propojení je entitou. Příkladem by byly týmy a hráčí; vazební tabulka bude obsahovat id hráče a id týmu, ale také další informace: kdy nastoupil, kdy ukončil, za kolik byl koupen. Taková tabulka bude mít vždy „divné jméno“, něco jako clen_tymu nebo hrac_tym. Ale jistě tam cítíš, že ty informace tam patří.

V tvém případě fotek u zboží by mohli uživatelé hodnotit, jak moc fotka odpovídá zboží. Tato informace (pomiňme kardinalitu) patří do vazební tabulky, protože fotka kočky bude mít smysl u plyšove kočky, ale ne u plyšového slona. Jakmile začneš vazební tabulku rozšířovat, může mít smysl dát jednotlivým záznamům identitu.

Je to praktičtější i při psaní aplikace. Je pohodlnější protáhnout phpkem, následně javascriptem a třeba ajaxem jedno id než dvě.

mimochodec:
Mně přijde praktičtější dvousloupcový klíč dle [#2].

V příkladu hráč x tým s možností odchodu a následného příchodu stejně nelze dvousloupcový index použít, tam je id na místě. Ale u tabulky z [#1] bych nějaké nové id necpal.

Ivan M.:
Nejsou to dva klíče - je to jeden klíč pro kombinaci obou sloupců. Pokud by to byly dva klíče, každý přes jeden sloupec (což nelze, ale lze to simulovat pomocí NOT NULL UNIQUE), tak by nešlo přiřadit jednu fotku více zboží ani více fotek jednomu zboží. Každé id by mohlo být ve sloupci jen jednou. Takto může každá kombinace být jen jednou (to zaručí, že jedna fotka nebude vícekrát u stejného zboží).

Jakýkoli index (a primární klíč jím je) může být přes více sloupců. Pozor trochu závisí na pořadí - mělo by odpovídat způsobu dotazů (nevím jaké máš povědomí o indexech). Takový se zapisuje ne za sloupec, ale na konec definice tabulky. I jednosloupcový klíč můžeš napsat na konec tabulky, ničemu to nevadí a běžně se to dělá.

References neslouží k dotazování. Je to integritní ochrana dat v databázi nazývaná cizí klíče. Například se ti kvůli ní nepodaří smazat fotku, která patří nějakému zboží (v takovém případě by zbyl záznam v té vazební tabulce s id neexistující fotky). Lepší takovou změnu zakázat než dovolit, aby v databázi byla chybná data (lze však nastavit, aby se raději smazalo vše, co by se stalo neplatné klauzulí ON DELETE CASCADE). Obdobně databáze nepovolí vložit záznam s id fotky, která neexistuje v tabulce fotek. Ne všechny enginy MySQL podoporují cizí klíče. (Znáš rozdíl mezi MyISAM a InnoDB?) Pokud používáš ten, který je nepodporuje, tak je můžeš uvést (a je to dobrý způsob dokumentace), ale databáze je bude ignorovat.

Podobně jako indexy i cizí klíče (ty klauzule REFERENCES) mohou být přes více sloupců. A v MySQL musí být cizí klíč uvedený až jako table constraint nikoli u sloupečku.


Asi nejpřehlednější schéma toho, co můžeš v CREATE TABLE uvést má SQLite (jde o jinou databázi než MySQL, nejsou kompatibilní, ale spoustu toho mají společného).
https://www.sqlite.org/lang_createtable.html
Pak to můžeš porovnat s http://dev.mysql.com/doc/refman/5.6/en/create-table.html. A nejspíš tě bude zajímat dokumentace cizích klíčů: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

juriad:
„

CREATE TABLE fotky_zbozi (   
  zbozi INT NOT NULL REFERENCES zbozi (id),   
  fotka INT NOT NULL REFERENCES fotky (id),   
  PRIMARY KEY (zbozi, fotka) 
);

“

Na platforme MySQL nemá časť REFERENCES v tomto zápise žiaden význam.

Dusann:
Máš pravdu, jde o 10 let starý bug.
MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

Takto je to funkčně správně (o důvod víc vždy psát všechny constraints až na konec):