podmínený DELETE ON CASCADE

knyttr
Nájdi si nejaké články o väzbe m:n.
Robí sa doplňujúcou tabuľkou, ktorá bude zviazaná s prvou aj z druhou tabuľkou.
A cez tú doplňujúcu tabuľku vznikne tá väzba.
Potom sa bude dať urobiť aj to káskádové mazanie alebo kaskádová aktualizácia.

knyttr
„FOREIGN KEY a ON CASCADE ale s vazbou m:n nepočítá -- počítá jedině s 1:n.“

Ale tá doplňujúca tabuľka by bola s oboma tabuľkami prepojená práve väzbou 1:n.
Prostredníctvom dvoch väzieb 1:n, by ti vznikla väzba m:n.
A tie kaskádové operácie budú fungovať vďaka obom tým väzbám 1:n súčasne.

knyttr
Najprv musíš prerobiť obe tabuľky tak, aby bol v každej nejaký jednoznačný identifikátor riadku tabuľky.
Potom v tej doplňujúcej tabuľke budeš mať dvojice tých identifikátorov.
A môžeš mať potom nastavené aj tie automatické kaskádové operácie.
Môže to byť priamo nastavené v návrhu databázy a ani nebude treba nejaké špeciálne kaskádové príkazy pre dotazy.

knyttr
Takže tebe vlastne z tej väzby, čo som popísal, nechýba tá prevodová tabuľka, ale jedna z tých dvoch, medzi ktorými má byť tá väzba m:n.
Tak si ju musíš dorobiť, v tom príklade čo si dal, bude mať dva riadky s číslami 1 a 2.
A návrh databázy zvolíš tak, že ak dáš požiadavku na zmazanie riadku s číslom 1, tak sa ti automatickou kaskádovou operáciou zmažú dva riadky v prevodovej tabuľke (ty si ju nazval první tabulka) a jeden riadok v druhej tabuľke (druha tabulka).
V MySQL v tabuľkách typu InnoDB by sa to malo dať.

Uvedená struktura je klasická třítabulková vazba m:n, kde chybí jedna z tabulek s daty. Pokud ta struktura vyhovuje, je podle mě z hlediska návrhu v pořádku. Snad bych jen přidal id i do té první tabulky, myslím, že je dobrý zvyk mít v každé tabulce umělý jednosloupcový klíč.
Přes kaskádování mě nenapadá, jak požadované fukčnosti dosáhnout. Myslím si, že to při zachování struktury nejde.

Řešení, které mě napadají, jsou:
* nemazat (fakt, že se daná barva právě nevyužívá, neznamená, že neexistuje a nebude využita v budoucnu. Bude-li v aplikaci nějaká nabídka barev, možná by tam měla být i nepoužívaná barva)
* triggery - s jejich pomocí lze dosáhnout požadovaného, ale nedokážu bez přemýšlení říct, jestli tam nebude problém s výkonností
* čas od času (třeba cronem) smazat nepoužívané barvy

Edit: _es
Pokud by tam ta tabulka měla být jenom se sloupcem id, pak je zbytečná. Kaskády (pokud si dobře pamatuju) se spouští pouze v případě, že je smazán záznam, na který se odkazuje. Tedy při smazání 1 z první tabulky se smažou i záznamy z vazební tabulky, která odkazují na tu 1. S druhou tabulkou se nestane vůbec nic, dál bude platit, že každý záznam ve vazební tabulce má podporu v obou tabulkách, z hlediska cizích klíčů je to tedy konzistentní stav.

TomášK
„Snad bych jen přidal id i do té první tabulky“
Id bude celá dvojica čísel, netreba pridávať ďalší stĺpec.

„Přes kaskádování mě nenapadá, jak požadované fukčnosti dosáhnout. Myslím si, že to při zachování struktury nejde. “
Ja som si skoro istý, že to cez tabuľky InnoDB pôjde, len treba všetky tie tri tabuľky správne vytvoriť a zadefinovať tie automatické kaskádové operácie.
A aktualizačné a mazacie dotazy nedávať na tú prevodovú tabuľku, ale na tie zvyšné dve podľa toho, ako to potrebuješ.
Zrejme asi len na tú tabuľku, ktorú si tu neuviedol.

Id bude celá dvojica čísel, netreba pridávať ďalší stĺpec.
Teoreticky ve většině tabulek nepotřebuješ umělý primární klíč, protože ho jde nahradit nějakým složeným, stejně tak i tady. Ale v okamžiku, kdy chceš odkazovat do té tabulky (a případy, kdy odkazování do vazební tabulky dává smysl, existují), oceníš jednosloupcový umělý klíč. A je jednodušší dát ho tam hned na začátku.

A aktualizačné a mazacie dotazy nedávať na tú prevodovú tabuľku
Ale to je přesně to, co on požaduje! Řekněme, že k uvedené struktuře přidáme tabulku 'sklad' (ať struktura odpovídá tobě navrhované), vazba pak bude ve významu 'barva je na skladě'. On chce postupně odstraňovat barvy ze skladu a případě, že barva není v žádném skladu, smazat barvu z tabulky. Ty mu radíš, ať maže rovnou celé sklady.

TomášK
prvni_id je id akého objektu?
druhy_id je id akého objektu?
Čo je vlastne treba odstraňovať a aké to má mať následky?
Skús to nejako zrozumiteľne jednoznačne definovať, možno ti poradí aj niekto iný.

prvni_id a druhe_id by v mnou navržené měly být pojmenované spíš sklad_id, barva_id, na pořadí nezáleží, pokud tvůj dotaz míří sem.

[#1]řádky v druhé tabulce budou vymazány, pokud je vymazána jejich vazba z první tabulky
Toto chápu tak, že se mažou záznamy ve vazební tabulce a jako důsledek jejich smazání se smaže záznam z datové tabulky, pokud už pro něj není žádná vazba.

TomášK
Môže to byť zabezpečené nepriamo, tak že sa nastaví, že bude zakázané zmazať riadok v druhej tabuľke s takým id, aký sa nachádza v prvej tabuľke.
Tak potom stačí po hocijakom vymazaní z prvej tabuľky dať príkaz vymazať aj riadok z druhej tabuľky s takým istým id.
A keď sa tam také id v prvej tabuľke ešte nachádza, tak sa vymazanie nepodarí, kvôli tomu zákazu.

Ano, s tím už souhlasím, to by fungovalo. Mám k tomu dvě výhrady:
1, Pokud už budu něco volat, pak se mi zdá rozumější rovnou příkaz, který vymaže odpovídající záznam, použití mechanismu kaskád se mi zdá zbytečné - myslím si, že bude pomalejší a z kódu se bude hůř číst, co chci udělat.
2, Aby se to zautomatizovalo, je potřeba trigger.

TomášK
Re: 1):
Ale na to aby si zistil, či ten záznam máš zmazať, musíš najprv zistiť počet záznamov s daným id - či je rovný nule.
Čo bude určite pomalšie, než zamietnutý dotaz na zmazanie jedného záznamu.
Tie kaskádové operácie sú rýchle, porovnávajú sa len indexy stĺpcov.
Okrem toho by si mal aj automaticky zaručenú integritu dát, do prvej tabuľky by nešiel zadať/pridať taký záznam, ktorý má id iné, aké sa nachádza v druhej tabuľke.
Rovnako tiež môžeš nastaviť, čo sa má stať pri zmene id v druhej tabuľke.

Re 2):
To bude určite pomalšie a menej spoľahlivejšie z hľadiska integrity, než obvykle zamietané mazacie dotazy.

Moc nechápu, jak se Vám podařilo udělat na druhé tabulce cizí klíč na druhy_id, když v první tabulce druhy_id není kompletní primární klíč. To je na tom mysql tak špatně, že toto povolí?

To zamítnutí smazání je přesně to, co potřebuji a přesně to, co se mi nedaří nikde najít :-)

Možná trigger before delete, který schválně vyvolá chybu - ale to je takové divné řešení.

Také si můžete vytvořit trigger after delete na druhé tabulce, který promaže nepotřebné řádky z první tabulky.

Ale, jak tu bylo zmíněno, normální je si tam ty údaje v první tabulce nechat, ničemu nevadí. Když chcete jen použité řádky, tak stačí jednoduchý join.

Kajman: první=väzobná tabuľka, druhá=jedna z tabuliek vo vzťahu m:n, teda ak to správne chápem...

Možná by bylo lepší, kdyby nyttr zobrazil sql dotazy na vytvoření tabulek včetně těch primárních a cizích klíčů. Nechápu, jak může mít cizí klíč zadefinovaný tak, že když maže něco z tabulky s cizím klíčem, tak mu to maže něco z tabulky s klíčem primárním souvisejícím s tím cizím.

_es
1, Myslím si, že to bude o něco málo rychlejší, ale podložené to nemám. Oba postupy využijí tytéž indexy, pro kaskádování bych čekal (zanedbatelně) větší náklady na zpracování.

Okrem toho by si mal aj automaticky zaručenú integritu dát, do prvej tabuľky by nešiel zadať/pridať taký záznam, ktorý má id iné, aké sa nachádza v druhej tabuľke.
Já navrhuju stejnou strukturu jako ty, jen nevyužívat kaskád na druhé tabulce při odstranění vazby. Z hlediska integrity to bude totéž. A také si můžu libovolně zvolit kaskádu pro druhou tabulku, nemusí to mít nutně RESTRICT.

2, Pokud jsem tě dobře pochopil, tak navrhuješ, aby ručně zavolal po každém mazání vazby další dotaz na odstranění záznamu z druhé tabulky, které případně selže. Já říkám, že ten druhý dotaz by měl být v triggeru, protože to bude jednak rychlejší a druhak spolehlivější z hlediska integrity, protože mám zaručené, že se zavolá vždy. Při tvém řešení se může stát, že ho na jednom místě někdo zapomene zavolat a integrita jde do háje.

knyttr
zamítnutí je pomocí ON DELETE RESTRICT

Kajman_
Nechápu, jak může mít cizí klíč zadefinovaný tak, že když maže něco z tabulky s cizím klíčem, tak mu to maže něco z tabulky s klíčem primárním souvisejícím s tím cizím.
Tohle právě nemá, ale chtěl by to udělat.

Trigger může být vykonán buď pro každý řádek zvlášť [FOR EACH ROW] nebo pro celý dotaz. Pokud ho vykonáváš pro každý řádek zvlášť, máš ke smazanému záznamu přístup pomocí OLD.nazev_sloupce, viz dokumentaci http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html Pokud bys měl trigger jen pro dotaz, myslím, že není způsob, jak zjistit smazané řádky.