Diskuze: MySQL UPDATE při nastavení omezení cizího klíče

petr.jouza

Člen

petr.jouza:30.6.2016 17:59

Ahoj,
potřeboval bych drobnou radu ohledně UPDATE v MySql (tabulky mám v INNODB).

Pro lepší pochopení dám raději příklad:

Mám tabulku uzivatele se sloupcem uzivatelske_jmeno, které je UNIQUE
Další tabulka např. ciselnik má sloupce vytvoril a upravil, které mají nastaveny relaci na uzivatelske_jmeno

Nyní bych chtěl provést, že když změním uživatelské jméno uživatele, aby hodnoty v číselníku (vytvořil, upravil) provázané na uživatelské jméno se také aktualizovaly (provedly UPDATE). U relací cizích klíčů jsem zkoušel různé vlastnosti ON UPDATE a když změním uživatelské jméno, tak mi MySQL stále dává chyby. Někde jsem se dočetl, že by měla zafungovat vlastnost ON UPDATE CASCADE, ale MySQL mi pořád nadává. Vůbec už netuším, jestli mi někde něco nechybí nebo, zda mé zamýšlení je vůbec možné.

Rozhodně se mi v PHP nechce psát tolik UPDATů pro každý sloupec tabulky. Vždyť ta databáze by to mohla sama obstarat.

Předem děkuji za nějaké tipy a rady.

Odpovědět

30.6.2016 17:59

shaman

Člen

shaman:1.7.2016 17:36

Ahoj,

Neviem ci rozumiem spravne, ale ked zmenis uzivatelske meno, tak jeho id sa nezmeni a teda nemusis menit ani id uzivatela v stlpcoch vytvoril a upravil.

V kazdom pripade nam napis query co si skusil a ako ti mysql nadava?

Nahoru Odpovědět

1.7.2016 17:36

try {...} catch (Exception ignored) { echo " ¯\_(ツ)_/¯ "; }

petr.jouza

Člen

petr.jouza:1.7.2016 17:54

Ahoj, já právě do toho vytvoril a upravil neukládám id uživatele, ale rovnou jeho uživatelské jméno. A když právě chci změnit uživatelské jméno, tak mysql říká, že v nějaké tabulce je právě kontrola cizího klíče, který ja měním.
Potřeboval bych právě, aby při změně uživatelského jména se změnily i hodnoty kam se cizí klíč odkazuje. Tedy vytvoril a upravil. (pokud to vůbec jde)

To s tim ukládáním id do vytvořil a upravil už me take napadlo, ale musel bych program předělat a nevím jestli pak jde nějak jednoduše bez pod dotazů udělat i select. Přijde mi právě lepší, ukládat to uživatelské jméno do vytvořil a upravil lepší (ale třeba se pletu).

pozn.: Používám databázový ovladač MySQL z tutoriálů zde od itnetwork a zatím jsem se nedostal k OOP, tak-že úpravy mých projektů jsou složitější.

Nahoru Odpovědět

1.7.2016 17:54

shaman

Člen

shaman:1.7.2016 18:11

Urcite sa tam dava id a nie meno uzivatela. to je princip relacnych databazi. Odporucam ti to prerobit. Je to dobre cvicenie a naucis sa pritom trosku zlozitejsie sql query, tak aby si ziskal meno z id v tom istom query.

Nahoru Odpovědět

1.7.2016 18:11

try {...} catch (Exception ignored) { echo " ¯\_(ツ)_/¯ "; }

petr.jouza

Člen

petr.jouza:3.7.2016 10:17

ok, ale principiálně by jinak mělo jít ne? Protože když v databázi definuji cizí klíče, tak mám nastavit události ON UPDATE a ON DELETE. Mám všude nastaveno ON UPDATE: CASCADE. Tím pádem by MySQL mělo umět zařídit, že při změně hodnoty změní i hodnoty ve všech ostatních tabulkách kam tento klíč odkazuje ne?

Jinak pokud provedu příkaz: UPDATE uzivatele SET uzivatelske_jmeno = jouzap WHERE id = 1, tak MySql vrátí chybu: Cannot add or update a child row: a foreign key constraint fails ('databaze'.'tabulka', CONSTRAINT 'omezeni₁' FOREIGN KEY ('vytvoril') REFERENCES 'uzivatele' ('uzivatelske_jmeno') ON UPDATE CASCADE)

To předělání mého projektu asi udělám, ale principiálně mi jde o to, zda to co jsem chtěl původně vůbec tedy jde... jestli i to co myslím je to správné nebo jestli to nejde vůbec.
Ale jinak díky za tipy

Nahoru Odpovědět

3.7.2016 10:17

Peter T

Člen

Peter T:3.7.2016 11:42

Ahoj, malo by sa to dať vyriešiť pomocou triggeru, skús si pozrieť
http://dev.mysql.com/…-syntax.html

Nahoru Odpovědět

3.7.2016 11:42

shaman

Člen

shaman:3.7.2016 16:19

ON UPDATE a ON DELETE je super vec a robi presne to co vlastne chces. V podstate tvoje riesenie nie je zle, ale nie je ani najlepsie. V tvojom pripade keby si pouzil jednoduchsi navrh (ten co som vyssie uviedol) tak by si nemusel nastavovat udalosti ON UPDATE/DELETE. A taktiez by ti SQL nevracalo tie chyby ze: Cannot add or update a child row.

Nahoru Odpovědět

3.7.2016 16:19

try {...} catch (Exception ignored) { echo " ¯\_(ツ)_/¯ "; }

petr.jouza

Člen

petr.jouza:5.7.2016 18:36

Ahoj,
neměl bys odkaz na nějaký český web? Nejsem moc veliký angličtin. Umím si přeložit nějaké základní technické věci, ale číst kompletní dokumentaci... to už je na mě moc...

Ale jinak díky... zkusím popátrat a snad najdu řešení mého problému.

Nahoru Odpovědět

5.7.2016 18:36

Peter T

Člen

Peter T:5.7.2016 19:12

Nemám návod, ale malo by to vyzerať nejak takto:

CREATE TRIGGER mytrigger AFTER UPDATE ON uzivatele // Po update tabulky uzivatele
FOR EACH ROW BEGIN

UPDATE ciselnik SET ciselnik.uzivatelske_jmeno = NEW.uzivatelske_jmeno //vykonaj update na novu hodnotu
WHERE ciselnik.uzivatelske_jmeno = OLD.uzivatelske_jmeno; //kde sa zhoduje stara hodnota

END;

Kód som neskúšal, takže neviem či zbehne hneď po copy&paste, keď tak ma prosím niekto opravte.

Hwvr, ako primárny kľúč ti odporúčam použiť číslo, ktoré sa nemení, a meno mať ako atribút, robí sa to tak a je to tak efektívnejšie ako vymýšlať procedúry a pod. ktoré budú zbytočne spomalovať vykonávanie. Navyše, dvaja používatelia môžu mať rovnaké meno, takže ani logicky nie je správne používať takýto atribút ako primary key.

Nahoru Odpovědět

5.7.2016 19:12

petr.jouza

Člen

petr.jouza:5.7.2016 19:33

Já mám v tabulce uzivatelske_jmeno jako UNIQUE a mám tam i IDčka jako primary key, které jsou AI, tak-že username nemůže být stejný u více uživatelů. Chtěl jsem však dát možnost uživatelské jméno změnit a jak tak koukám, tak se snad ani nebudu zaobírat tedy těmi trigery a rovnou to předělám na ty IDčka... Je pravda, že ta DB by s tím měla akorát zbytečnou práci, i když by to mělo být výjimečné (změna username a mohl by to provést pouze admin, nikoliv uživatel sám).

Spíš si tedy budu muset nastudovat, jak si zobrazit při výpisu tabulky uživatelské jméno, abych to nedělal dvěma dotazy do DB a to ještě tak nešťastně jako:

Ukázka:

<?php
$ciselnik = Db::queryAll('SELECT nazev,zkratka,vytvoril,upravil FROM ciselnik') ;
foreach ($ciselnik as $polozka) {
        echo $polozka['nazev'] ;
        echo $polozka['zkratka'] ;
        $vytvoril = Db::queryOne('SELECT uzivatelske_jmeno FROM uzivatele WHERE vytvoril = ? LIMIT = 1',$polozka['vytvoril']) ;
        echo $vytvoril['uzivatelske_jmeno'] ;
        $upravil = Db::queryOne('SELECT uzivatelske_jmeno FROM uzivatele WHERE upravil = ? LIMIT = 1',$polozka['upravil']) ;
        echo $upravil['uzivatelske_jmeno'] ;
}
?>

¨
Toto je jistě veliká prasečina.

Mělo by to určitě tedy jít v jednom dotazu. Již jsem o tom něco málo četl... tak snad to dám dohromady.
Asi by mi mělo pomoci toto: http://www.itnetwork.cz/…vice-tabulek že?

Ale stejně díky.

Nahoru Odpovědět

5.7.2016 19:33

Peter T

Člen

Peter T:7.7.2016 21:04

Áno, na to sa používa JOIN, malo by to vyzerať nejak takto:

SELECT * FROM uzivatele u
JOIN ciselnik c ON c.uzivatele_id = u.id

To spôsobí, že ti SELECT vráti tabuľky spojené a dáta namapované tak, že ku každému ID v uzivatele priradí správnu hodnotu z ciselnik. Môžes skúsiť pozrieť cvičenia z predmetu DBS z mojej školy:
Cvičenia
Riešenia

Miesto * môžeš samozrejme dať vypísať len stĺpce, ktoré chceš.

uzivatele_id by predstavoval cudzí kľúč v tabuľke ciselnik a reprezentoval by riadok z tabuľky uzivatele, ktorému prislúcha. Vďaka tomu bude zmena užívateľského mena jednoduchá, lebo užívateľ bude v DB reprezentovaný jedinečným ID. A Ak chceš aj uzivatelske_jmeno jedinečné, stačí pri tvorbe tabuľky definovať constraint UNIQUE.

Tu ti dávam ešte vysvetlenie LEFT/RIGHT/INNER/.. joinov pomocou vennových diagramov, mne to pomohlo:
https://blog.codinghorror.com/…f-sql-joins/

Editováno 7.7.2016 21:05

Akceptované řešení
+20 Zkušeností
+2,50 Kč

Nahoru Odpovědět

7.7.2016 21:04

petr.jouza

Člen

petr.jouza:10.7.2016 10:29

Díky moc. To je to co jsem potřeboval. Využil jsem tutoriálu zde z IT network o MySQL: http://www.itnetwork.cz/…vice-tabulek
Nejdříve jsem se toho docela bál, ale je to primitivní.

Jen jsem musel vyřešit nejednoznačnost sloupců. Mám totiž ve všech tabulkách název primárního AI sloupce 'id', tak že musím uvádět název tabulky a pak sloupec (př.: uzivatele.id). Pak mám v docela dost tabulkách název sloupce 'zkratka' a PHP mi při použití $hledani['tabulka.sloupec'] zamrzá. To je asi špatně použité. Tak se asi budu muset vrhnout ještě do změn názvů sloupců... aby byli jednoznačné a nemusel jsem řešit takové zbytečné problémy.

Ale jinak ještě jednou díky.

Nahoru Odpovědět

10.7.2016 10:29

Peter T

Člen

Peter T:10.7.2016 21:45

Názvy stĺpcov by som nemenil. Napríklad stĺpec ID (primary key) by som nazval v každej tabuľke rovnako, a pri selectovaní by som použil aliasy, napríklad:

SELECT c.id AS cid, u.id AS uid from uzivatele u
JOIN ciselnik c ON c.uzivatele_id = uid

A v PHP potom bude názov stĺpca cid a uid.
Celkovo by som sa snažil stĺpce reprezentujúce rovnakú vec nazvať rovnako, aby to dodržovalo istú konvenciu a čitatelnosť

Editováno 10.7.2016 21:46

Nahoru Odpovědět

10.7.2016 21:45

petr.jouza

Člen

petr.jouza:11.7.2016 20:41

Tak to jsem netušil, že MySQL umí používat aliasy. Super věc. Podařilo se mi v PHP jeden takový složitější dotaz napsat a dokonce jsem ušetřil na jedné stránce 2 SQL dotazy, protože jsem to scucnul do jednoho.
Super věc. Koukám, že se mám ještě co učit.

Ještě jednou díky.

Nahoru Odpovědět

11.7.2016 20:41

petr.jouza

Člen

petr.jouza:12.7.2016 17:52

Tak jsem se s tím INNER JOIN docela spřátelil... Jen jsem teď narazil na neřešitelný problém (alespoň pro mne).

Potřeboval bych v tabulce uzivatele, kde mám sloupce id, uzivatelske_jmeno, vytvoril, upravil a v tom vytvoril a upravil mám id z té samé tabulky, tak abych v SQL dotazu zobrazil v těchto sloupcích uzivatelske_jmeno.
Myslel jsem, že budu muset tu tabulku uzivatele načíst JOINem také, ale přitom mám chybu, že nemám unikátní tabulku nebo alias v tabulce uzivatele: Not unique table/alias: 'uzivatele'.
To ale asi tímto způsobem nejde že?

Nevíš náhodou jak tohoto docílit? Abych nemusel dělat další query do té samé tabulky?

Nahoru Odpovědět

12.7.2016 17:52

Peter T

Člen

Peter T:12.7.2016 18:53

Myslím si, že sa to dá jednoducho docieliť JOINom samého so sebou (uzivatele x uzivatele x uzivatele):

SELECT u1.uzivatelske_jmeno, u2.uzivatelske_jmeno AS vytvoril, u3.uzivatelske_jmeno AS upravil  FROM uzivatele u1
JOIN uzivatele u2 ON u2.id = u1.vytvoril
JOIN uzivatele u3 ON u3.id = u1.upravil

SELECT vráti 3 stĺpce, v ktorom budú postupne uzivatelske_jmeno, meno toho, kto ho vytvoril, meno toho, kto ho upravil.

Nakoľko úprava sa bude pravdepodobne dať vykonať viackrát, bolo by lepšie to riešiť pomocnou, tzv. väzobnou tabuľkou, v nej by boli atribúty id_pouzivatela a id_upravil, prípadne aj dátum úpravy (pre lepší prehľad). A keď už by sme mali väzobnú tabuľku, môžeme do nej rovno pridať aj informáciu o tom, kto účet vytvoril (buď ďalší atribút "vytvoril", alebo môžeš použiť nejaký FLAG (0 = vytvoril, 1 = upravil)

SELECT u1.uzivatelske_jmeno AS uzivatel, u2.uzivatelske_jmeno AS upravil, uu.datum_upravy FROM upravy_uzivatelov uu
JOIN uzivatele u1 ON u1.id = uu.id_pouzivatela
JOIN uzivatele u2 ON u2.id = uu.id_upravil
where upravil_vytvoril_FLAG = 1 -- tj iba riadky úprav

Nahoru Odpovědět

12.7.2016 18:53

petr.jouza

Člen

petr.jouza:13.7.2016 16:12

Tak to nakonec šlo. Trochu jsem s tím bojoval, ale přemluvil jsem ho

já jsem předtím nepoužil to "FROM uzivatele u". Jestli chápu správně, tak to "u" je jako alias té tabulky?
Já se právě na sloupce odkazoval ne přes alias tabulky, ale přímo na tu tabulku a v tom byl hlavní zádrhel. Když jsem to upravil podle toho co jsi napsal, tak to funguje perfektně.

Nahoru Odpovědět

13.7.2016 16:12

Peter T

Člen

Peter T:13.7.2016 19:17

Tak ako píšeš "u" slúži ako alias pre tabuľku

Nahoru Odpovědět

13.7.2016 19:17

petr.jouza

Člen

petr.jouza:14.7.2016 15:39

Super... díky...

A jestli se mohu ještě zeptat... dá se něco takového zařídit i obráceně do INSERTu?
Dám příklad:
v php do HTML formuláře třeba selectu načtu číselník s organizačními jednotkami, kde člověk, který vytváří nějakého nového uživatele vybere právě nějakou hodnotu. Do položky value="" ale nenačtu id-čka z databáze, ale nějaké zkratky. K uživateli chci ale uložit do pole organizační jednotka id z toho číselníku na základě zvolené zkratky. Musím tedy nejdříve provést dotaz na databázi do číselníku, aby mi vrátila ID, které uložím k uživateli nebo to lze v rámci jednoho dotazu přes insert nějak zařídit?

Na toto jsem zde v manuálech nikde nenarazil a ani na docela hezky a stručně popsané stránce http://www.junext.net/mysql/ nebo jen nevím, jaká funkce (příkaz) se k tomu používá.

Nahoru Odpovědět

14.7.2016 15:39

Peter T

Člen

Peter T:14.7.2016 20:17

Niesom si istý či rozumiem, ale asi smeruješ k INSERT INTO .. SELECT. Príklad:

INSERT INTO org_jednotka(id_skratky)
SELECT id FROM ciselnik WHERE skratka = 'MOJA_SKRATKA'

kde tabuľka org_jednotka je nejaka tabuľka, do ktorej chceš do atribútu id_skratky uložiť ID tejto skratky, a tabuľka ciselnik obsahuje atribúty id, skratka. My teda chceme do tabuľky org_jednotka zapísať id skratky, ktorá sa zobrazuje na frontende (MOJA_SKRATKA), a tak získame z tabuľky ciselnik id tejto skratky, ktoré pošleme do INSERTu.

Dúfam že je to to, čo si potreboval Ešte upozorňujem že som robil s PostgreSQL a s mySQL mám menej skúseností tak neviem či to, čo píšem bude s rovnakou syntaxou fungovať aj pre mySQL, ale malo by

Editováno 14.7.2016 20:18

Nahoru Odpovědět

14.7.2016 20:17

petr.jouza

Člen

petr.jouza:15.7.2016 7:50

Myslel jsem to trochu jinak. Zkusím to více dát do příkladu:

Mám tabulku uživatelé se sloupci:

id

uzivatelske_jmeno

další nějaké sloupce...

organizacni_jednotka

další nějaké sloupce...

Pak mám tabulku číselníku organizačních jednotek:

id

zkratka

nazev

další nějaké sloupce....

Relace je nastavena mezi sloupcem organizacni_jednotka v tabulce uzivatele na id v tabulce ciselnik_organizacni_jednotky

Do webové stránky pro založení nového uživatele načtu do selectu číselník organizačních jednotek:

<select name="organizacni-jednotka">
        echo '<option value="'.$organizacniJednotka['zkratka'].'">'.$organizacniJednotka['nazev'].'</option>' ;
</select>

Tak-že při založení nového uživatele odešlu postem zkratku z organizační jednotky, ale do tabulky uživatelé potřebuji uložit id z toho číselníku.

Nechci právě do selectu přímo načítat idčko...
Zatím bych to udělal právě takto (zjednodušeně napsáno):

<?php

$org_jednotka = Db::queryOne('SELECT id FROM ciselnik_organizacni_jednotky WHERE zkratka = ?',$_POST['organizacni-jednotka']) ;

Db::query('INSERT INTO uzivatele (organizacni_jednotka) VALUES (?)',$org_jednotka['id']) ;

?>

Říkám si tedy však, že by to mělo jít zařídit jedním dotazem, že bych si nějak vyselectoval to IDčko a dal ho do tabulky uživatelů v jednom dotazu... a ne ve dvou jako jsem udělal příklad výše.

Zatím co jsi mi psal, tak jsem to dal dohromady... možná s velice drobnou úpravou, ale jinak to funguje
Stačí mě trochu nakopnout a pak se v tom docela vyznám a nějak to dám dohromady

Díky

Nahoru Odpovědět

15.7.2016 7:50

Peter T

Člen

Peter T:15.7.2016 18:19

V PHP som naposledy robil pred dvoma rokmi, takže sa trocha strácam v tých kódoch, ale nerozumiem, prečo je tento kód nevhodný:

INSERT INTO org_jednotka(id_skratky)
SELECT id FROM ciselnik WHERE skratka = 'MOJA_SKRATKA'

Jedná sa o jeden dopyt čo sa týka atomickosti.
Alebo môžeš SELECTOM získať aj id aj skratku, namapovať to do objektu s atribútmi napr. int id, string skratka, a na frontende v dákom choiceboxe (alebo v čom zobrazuješ tie skratky), vypísať len atribút skratka z toho objektu (Pracujem s Javou, tak prepáč, ak je niečo z toho v PHP neuskutočniteľné )

Nahoru Odpovědět

15.7.2016 18:19

petr.jouza

Člen

petr.jouza:15.7.2016 18:49

V PHP si s tím nějak poradím... jedná se mi o poskládání dotazu do databáze. V tom mém příkladu výše mám právě dotazy 2 a jde mi o to, zda se to dá scucnout do jednoho.

Teď mě napadla taková věc.. ale nevím jestli by mohla fungovat:

INSERT INTO uzivatele (organizacni_jednotka)
VALUES (SELECT id FROM ciselnik_organizacni_jednotky WHERE zkratka = ?)

Do otazníku pak doplním ten odeslaný formulář z webu (tedy mojí zkratku)...
Teď jsem to i zkusil, ale hlásí to chybu v syntaxi.

Nevím, jestli lze udělat něco jako proměnou v SQL a v jednom query se na to odkázat.

Jinak já pracuji normálně s webem a stránky zobrazuji ve standartních prohlížečích (IE, Chrome, Mozilla atd...)

No asi budu muset udělat vždy dotaz na ty číselníky a načíst z toho idčka. To díky JOINům mohu udělat v jednom dotazu a pak dosazovat do INSERTu potřebné hodnoty.

Nahoru Odpovědět

15.7.2016 18:49

Peter T

Člen

Peter T:18.7.2016 20:51

No veď presne na toto som ti kód napísal, je to ten INSERT .. SELECT, to je jeden príkaz s významom subselectu, ktorý si napísal.
Pozri si dokumentáciu aby si pochopil syntax:
http://dev.mysql.com/…-select.html

INSERT INTO uzivatele (organizacni_jednotka)
SELECT id FROM ciselnik_organizacni_jednotky WHERE zkratka = ?

Podotýkam, je to jedno query, nie dva príkazy

Nahoru Odpovědět

18.7.2016 20:51