Dotaz: mysql zavislost vice tabulek - cizi klic?

[fg]

Mam nasledujici schema:

tabulka1
jmeno heslo

tabulka2
jmeno data1 data2 ...

tabulka3
jmeno data1 da...

tabulka...

Potreboval bych, aby kdyz uzivatel1 existuje alespon v nejake z tabulek tabulka2-tabulkaN, tak aby existoval i v tabulka1. Pokud by se vymazal uzivatel1 z nejake tabulky tabulka2-tabulkaN a uz v zadne z techto tabulek neexistoval, tak by se automaticky vymazal i z tabulka1. Vim jak tohle vyresit pomoci PHP, ale chtel bych si to co mozna nejvice zjednodusit a pokud tohle pujde udelat primo pomoci mysql, budu rad. Pujde to, jak?

Pridani novych zaznamu by melo fungovat nejak takhle: pokud uzivatele1 pridam napr. do tabulka2, tak se automaticky prida do tabulka1 s heslem ze sloupce data1(v tabulka2). Potom uz si hesla nevsimam a jakakoliv zmena sloupce heslo ci sloupcu data1 v ostatnich tabulkach bude zcela nezavisla.

Mam pocit, ze pokud nepujde to ostatni, tak alespon to automaticke mazani z tabulka1 v pripade neexistence uzivatele1 v ostatnich tabulkach by se dala udelat pomoci ciziho klice?

Odpovědi

[FooBar]

No... prijde mi, ze chces cizi klic z tabulka2...N na sloupci jmeno na zaznam v tabulka1;)

Ocekavej ale, ze narazis na takove vesele komplikaci, ze velmi casto pouzivany (mozna implicitni?) engine MySQL, zvany MyISAM, referencni integritu vubec neumi a napsat tam takovej constraint je pomalu na urovni komentare.;) Tudiz, pokud pouzivas MyISAM, nepomuzes si, musis prejit napr. na InnoDB.

[fg]

Jenže já chci aby když v tabulka2 přidám uživatele, aby se přidal i v tabulka1, zatímco takový cizí klíč očekává že už v tabulka1 uživatel bude existovat. Takto by to mělo fungovat(tabulka: sloupec s uživateli):

tabulka1: uživatel1 uživatel2 uživatel3
tabulka2: uživatel1
tabulka3: uživatel2 uživatel1
tabulka4: uživatel3


toto už správně není, protože uživatel1 neexistuje v žádných dalších tabulkách
tabulka1: uživatel1 uživatel2
tabulka2: 
tabulka3: uživatel2
tabulka4: uživatel3

ani toto není správně, protože uživatel1 existuje v tabulka2, ale ne v tabulka1
tabulka1: uživatel2 uživatel3
tabulka2: uživatel1
tabulka3: uživatel2
tabulka4: uživatel3

Na to bych asi potřeboval nějaké sofistikovanější propojení. Poradí někdo?

[happy barney]

použi triggre a refcounter

[fg]

Jaký refcounter? Jako že bych na každé z podřízených tabulek vytvořil trigger, že při insert se pokud neexistuje založí záznam v hlavní a při každém vytvoření bych v tabulka1 v sloupci pocet připočet jedničku. Pokud bych v podřízených tabulkých mazal, tak bych dekrementoval hodnotu v primární dokud by nebyla 0, v tom případě bych záznam odstranil. Byla ta rada tak myšlena nebo se to dá udělat jednodušeji?

[happy barney]

áno, presne to som myslel. V prípade pridania/zmazania tabuľky nemusíš modifikovať všetko, len príslušnú tabuľku

[fg]

Tak jsem chtěl dát ten mazací trigger přímo na hlavní tabulku, ale nejde spustit:

CREATE TRIGGER tr_deleteNotUsed AFTER UPDATE ON users
  FOR EACH ROW BEGIN
    DELETE FROM users WHERE username=NEW.username AND used=0;
  END;

po update tabulky to vrací: #1442 - Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 

Dočetl jsem se že by to mohlo být kvůli zacyklení, ale tady se přeci nic necyklí. Kde dělám chybu?

[rADOn]

Pokousis se cyklicky mazat sam sebe coz je blbost. Na cisteni potrebujes vlastni triggery na kazde datove tabuli, ktere budou udrzovat pocet referenci a pripadne mazat uzivatele z hlavni tabule. Cili trigger mas vicemene dobre ale davas ho na spatnou tabuli.

[FooBar]

Aha, vida, zmatlo mne zadani, ponevadz sice mluvis o sloupci heslo, ale to mas jen u tabulky 1 :)

V tom pripade je to jasnej pripad pro triggery, ale prosimte, ten navrh zni dost silene. Zvazil bych, jestli to treba nechces delat jinak :)

[Tarmaq]

nevim co vsechno umi MySQL, dlouho jsem s tim uz nedelal..
Osobne bych to udelal tak, ze bych udelal pohled nad tabulkou1, joinujici tabulku2, tabulku3 .. tabulkuN a v insert/update triggeru vyresil rozstrkani prislusnych veci do tabulek.. u delete triggeru by to fungovalo podobne, mazalo by se z tabulky1 a o smazani ostatnich by se postaralo ON DELETE CASCADE na tom cizim klici

[fg]

Jenže tak to fungovat nesmí. Po smazání uživatele z tabulka1(nešťastnou náhodou) se nesmí smazat nikde jinde.

[Tarmaq]

nevim, mne to prijde jako hodne nestastne reseni navrhu databaze, to budou uzivatele naraz v X tabulkach a zadna z nich nebude autoritativni?

[fg]

Každá databáze = 1 aplikace. Pro autentizaci uživatelů se používá tabulka1. Ale pokud odstraním uživatele z tabulka1, nemohu ho smazat i z ostatních tabulek(protože to není jen o mazání záznamů, několik tabulek má přímou souvislost se soubory na pevném disku).. Jinak zdá se že jsem to už vyřešil. Vytvořil jsem rutinu, kterou spouští trigger na každé z podřízených db:

DELIMITER $$

CREATE TRIGGER tr_incrementMain AFTER INSERT ON users
  FOR EACH ROW BEGIN
    CALL auth.pr_authUsed(NEW.username,NEW.password,1);
  END;

DELIMITER $$

CREATE TRIGGER tr_decrementMain AFTER DELETE ON users
  FOR EACH ROW BEGIN
    CALL auth.pr_authUsed(OLD.username,"",0);
  END;




DELIMITER $$

CREATE PROCEDURE pr_authUsed (uzivatel CHAR(255), heslo CHAR(255), posun INT)
BEGIN
DECLARE usedValue INT; 
 IF (posun = 1) THEN BEGIN
  UPDATE `auth`.`users` SET `used` = used+1 WHERE `users`.`username` = uzivatel LIMIT 1;
  IF (ROW_COUNT()<1) THEN BEGIN
    INSERT INTO `auth`.`users` (`username`, `password`, `active`, `used`) VALUES (uzivatel,heslo,'1','1');
  END;
  END IF;
 END;
 ELSE BEGIN
  UPDATE `auth`.`users` SET `used` = used-1 WHERE `users`.`username` = uzivatel LIMIT 1 ;
  SELECT `used` INTO usedValue FROM `auth`.`users` WHERE `users`.`username` = uzivatel LIMIT 1 ;
  IF (usedValue < 1) THEN BEGIN
   DELETE FROM `auth`.`users` WHERE `username` = uzivatel LIMIT 1 ;
  END;
  END IF;
 END;
 END IF;
END

[Tarmaq]

V tom pripade bych uzivatele z tabulky1 vubec nemazal, jen bych mel pro kazdy modul/aplikaci samostatny sloupec, ktery by urcoval jestli tam ma pristup.. misto odstraneni z nejakeho modulu bych mu akorat zakazal pristup v tabulce1 a data by pritom zustala zachovana..
Pokud je ale ten trigger vyhovujici, tak asi neni co resit..