Autor Zpráva
knyttr
Profil *
Dobrý den,

mám dvě navázané tabulky přes FOREIGN KEY, nicméně vazba není 1:n, ale m:n. To znamená, že více řádků v první tabulce může odkazovat na jeden řádek v tabulce druhé, ale zároveň i jeden řádek v první tabulce může odkazovat na více řádků v tabulce druhé. Potřeboval bych dosáhnout něčeho podobného, jako ON CASCADE (řádky v druhé tabulce budou vymazány, pokud je vymazána jejich vazba z první tabulky), ale jen za předpokladu, že již žádný další řádek v první tabulce na daný záznam v tabulce druhé neodkazuje. Názorný příklad:

první tabulka:

prvni_id | druhy_id
-----------------------
1 | 23
2 | 23
1 | 24
2 | 25

druha tabulka:

druhy_id | barva
--------------------
23 | cervena
24 | modra
25 | cerna

Pokud provedu DELETE FROM prvni_tabulka WHERE prvni_id = 1; potřebuji, aby se přes CASCADE z druhe tabulky vymazal zaznam kde druhy_id = 24, ale rozhodne ne druhy_id=23, protože na něj stále odkazuje prvni_id=1. Toho se mi přes CASCADE nedaří dosáhnout.

Na co se mám zaměřit? Triggery? Procedury?

Díky za cokoliv.

Vojta
knyttr
Profil *
chtěl jsem říci:

..., ale rozhodne ne druhy_id=23, protože na něj stále odkazuje prvni_id=2. (uvedl jsem špatnou hodnotu prvni_id)
_es
Profil
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
Profil *
Vazbě m:n rozumím.

FOREIGN KEY a ON CASCADE ale s vazbou m:n nepočítá -- počítá jedině s 1:n. Hledám nějaké jiné řešení, které se s tímto vypořádá.

"Potom sa bude dať urobiť aj to káskádové mazanie alebo kaskádová aktualizácia."

Pokud o nějakém řešení víš, sem s ním.
_es
Profil
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
Profil *
Dobrá tedy. A jak bys udělal například doplňující tabulku pro ten můj jednoduchý příklad? :)
_es
Profil
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
Profil *
To nikam nevede, protože to už v podstatě zastupuje ta první tabulka. Druhá tabulka má jednoznačný identifikátor a ta první jen na něj ukazuje.
_es
Profil
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ť.
TomášK
Profil
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.
_es
Profil
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.
TomášK
Profil
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.
_es
Profil
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ý.
TomášK
Profil
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.
_es
Profil
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.
TomášK
Profil
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.
_es
Profil
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.
knyttr
Profil *
_es:

To zamítnutí smazání je přesně to, co potřebuji a přesně to, co se mi nedaří nikde najít :-)
Kajman_
Profil *
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.
tiso
Profil
Kajman: první=väzobná tabuľka, druhá=jedna z tabuliek vo vzťahu m:n, teda ak to správne chápem...
Kajman_
Profil *
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.
TomášK
Profil
_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.
knyttr
Profil *
TomášK:

to, co popisuješ v druhém bodu, se mi zdá nejreálnější a něco takového jsem si od počátku představoval. A teď tedy praktická realizace.

Vytvořím trigger after delete takový, že
a/ projde celou druhou tabulku a bude hledat, kde chybí vazby z první tabulky a ty vymaže
b/ tomu triggeru je možné předat, která hodnota byla vymazána (zda je tohle možné skutečně nevím, v dokumentaci jsem to nenašel), a tak nemusím procházet celou tabulku, ale sáhnu jen na ty konkrétní hodnoty

Díky, jsem rád, že to někam spěje :-)
TomášK
Profil
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.
knyttr
Profil *
Dle dokumentace na http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html je jediná možnost použití FOR EACH ROW, tedy aplikace na všechny řádky, ale rozhodně ne na celý dotaz. Nebo se pletu?
knyttr
Profil *
tak již vyřešeno. díky všem.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: