innoDB - relacje, nie można usunąć rekordu z powodu relacji Opcje

[VGT]

Mam spore trudności z rozwiązaniem problemu z relacjami w aktualnie tworzonej strukturze danych.

Sa tu 4 tabele:



1. rachunki
- pozwala na zdefinoiwanie wielu rachunkow, dla ktorych beda rejestrowane operacje
- relacja 1-do-wielu z tabela grupy. Mozliwosc zdefiniowania wielu grup dla każdego z rachunkow (sluza one do grupowania operacji). w relacji zdefiniowane 'on delete cascade' (usuniecie rachunku ma czyscic wszystkie informacje z nim powiazane)
- relacja 1-do-wielu z tabela operacje 'on delete cascade' (jw.)

2. grupy
- mozliwosc zdeiniowania grup dla operacji
- relacja 1-do-wielu z tabela operacje. brak autoamtycznego kasowania operacji. Chodzi o to, aby niemozliwe bylo usuniecie grup, do ktorych juz sa przyporzadkowane operacje

3. operacje
- relacja 1-do-1 z tabela opisy. Ma na celu wyciagniecie opisow z tabeli operacje, gdyz i tak potrzebne sa rzadko a bywaja dlugie. 'on delete cascade'

4. opisy do operacji

Teraz jesli wypelniam tabele danymi (przykladowy kod do uzycia na koncu posta) i probuje usunac dany rachunek, wyskakuje blad z relacjami. Zakladam, ze chodzi o jakby podwojna rejacle pomiedzy rachunkami i operacjami (jedna bezposrednia a druga poprzez tabele grupy).
Czyli najprawdopodobniej przy usuwaniu rachunku dziala relacja do usuwania grup nalezacych do tego rachunku, ktore jednak nie moga byc usuniete, gdyz sa przyporzadkowane do nich operacje. Ale jesli by zdefiniowac kolejnosc dzialania relacji (przy usuwaniu rachunku najpierw usunac operacje do niego nalezace, adopiero potem grupy), wtedy wydaje mi sie, ze wszystko by zadzialalo. Nie wiem tylko jak to zrobic


struktura tabel + przykladowe dane:

[SQL] pobierz, plaintext 
-- MySQL dump 10.10
--
-- Host: localhost    Database: rachunki
-- ------------------------------------------------------
-- Server version	5.0.24a-community-nt
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `grupy`
--
 
DROP TABLE IF EXISTS `grupy`;
CREATE TABLE `grupy` (
  `id_grupa` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_rachunek` int(10) UNSIGNED NOT NULL,
  `grupa` varchar(127) collate utf8_polish_ci NOT NULL,
  PRIMARY KEY  (`id_grupa`),
  KEY `id_rachunek` (`id_rachunek`),
  CONSTRAINT `grupy_ibfk_1` FOREIGN KEY (`id_rachunek`) REFERENCES `rachunki` (`id_rachunek`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
 
--
-- Table structure for table `operacje`
--
 
DROP TABLE IF EXISTS `operacje`;
CREATE TABLE `operacje` (
  `id_operacja` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_rachunek` int(10) UNSIGNED NOT NULL,
  `data` date NOT NULL,
  `typ` enum('przychod','rozchod') collate utf8_polish_ci NOT NULL,
  `kwota` int(10) UNSIGNED NOT NULL,
  `id_grupa` int(10) UNSIGNED NOT NULL,
  `opis_krotki` char(32) collate utf8_polish_ci NOT NULL,
  PRIMARY KEY  (`id_operacja`),
  KEY `id_grupa` (`id_grupa`),
  KEY `id_rachunek` (`id_rachunek`),
  KEY `data` (`data`),
  KEY `typ` (`typ`),
  CONSTRAINT `operacje_ibfk_1` FOREIGN KEY (`id_grupa`) REFERENCES `grupy` (`id_grupa`),
  CONSTRAINT `operacje_ibfk_2` FOREIGN KEY (`id_rachunek`) REFERENCES `rachunki` (`id_rachunek`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
 
--
-- Table structure for table `opisy`
--
 
DROP TABLE IF EXISTS `opisy`;
CREATE TABLE `opisy` (
  `id_opis` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_operacja` int(10) UNSIGNED NOT NULL,
  `opis` text collate utf8_polish_ci NOT NULL,
  PRIMARY KEY  (`id_opis`),
  KEY `id_operacja` (`id_operacja`),
  CONSTRAINT `opisy_ibfk_1` FOREIGN KEY (`id_operacja`) REFERENCES `operacje` (`id_operacja`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
 
--
-- Table structure for table `rachunki`
--
 
DROP TABLE IF EXISTS `rachunki`;
CREATE TABLE `rachunki` (
  `id_rachunek` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rachunek` varchar(127) collate utf8_polish_ci NOT NULL,
  PRIMARY KEY  (`id_rachunek`),
  UNIQUE KEY `rachunek` (`rachunek`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
INSERT INTO `rachunki` (`rachunek`) VALUES ('nazwa_rachunku');
INSERT INTO `grupy` (`id_rachunek`,`grupa`) VALUES (1,'nazwa_grupy_1');
INSERT INTO `grupy` (`id_rachunek`,`grupa`) VALUES (1,'nazwa_grupy_2');
INSERT INTO `operacje` (`id_rachunek`,`data`,`typ`,`kwota`,`id_grupa`,`opis_krotki`) VALUES(1,'2006-12-03','przychod',21,1,'opis');
INSERT INTO `opisy` (`id_operacja`,`opis`) VALUES (1,'opis dlugi2');
[SQL] pobierz, plaintext 

Teraz jesli sprobuje na takich danych wykonac zapytanie:

[SQL] pobierz, plaintext 
DELETE FROM `rachunki` WHERE `id_rachunek` =1
[SQL] pobierz, plaintext 

Otrzymam blad:

Kod

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`rachunki/operacje`, CONSTRAINT `operacje_ibfk_1` FOREIGN KEY (`id_grupa`) REFERENCES `grupy` (`id_grupa`))

Mam nadzieje, ze nie zakrecilem zbyt mocno i bede wdzieczny jesli ktos mnie nakieruje na rozwiazanie tego problemu.

Ten post edytował Bonastick 3.12.2006, 13:02:10

[dr_bonzo]

Hmm, moze to przez to:

[SQL] pobierz, plaintext 
CONSTRAINT `operacje_ibfk_1` FOREIGN KEY (`id_grupa`) REFERENCES `grupy` (`id_grupa`),
[SQL] pobierz, plaintext 

-- nie wiem jakie jest domyslen zachowanie ON DELETE ale wyglada na to ze RESTRICT.

[VGT]

Tez wydaje mi sie ze domyslne zachowanie to RESTRICT, ale taki jest wlasnie cel. W opisie kolejnych tabel napisalem, ze ma byc zablokowana mozliwosc usuwania grup, ktore wciaz maja przypisane do siebie operacje i faktycznie przez to jest blad.

Tylko mi nie chodzi o rozwiazanie w postaci zniesienia RESTRICT - ono ma tam być.

Mi chodzi o zmiane kolejnosci dzialania relacji: jesli najpierw zadziala relacja do tabeli operacje, to wszyskie operacje wraz z opisami z tabeli `opisy` zostana usuniete i gdy rozpocznie sie usuwanie grup, juz zadnej operacji przypisanej do tych grup nie bedzie.

Nie wiem, czy to jest w ogole mozliwe do zrealizowania w tej postaci, ale moj projekt zdecydowanie nie jest skomplikowany i bywaja duzo bardziej zlozone, wiec wydaje mi sie ze rozwiazanie jest tyle ze przy moim pierwszym podejsciu do innoDB po prostu jeszcze go nie znam

***EDIT***

Kombinowałem dalej i jednak się udało. Znalazłem rozwiązanie problemu, więc gdyby kiedyś ktoś miał podobny:

ROZWIĄZANIE:
Wyglada na to, ze klucze obce sa wykonywane w kolejnosci alfabetycznej. Nazwy dwoch klucz zazebiajacych sie, to: 'grupy_ibfk_1' i 'operacje_ibfk_1'.
Kiedy na poczatku tego pierwszego dopisalem "z" (zgrupy_ibfk_1), wszystko ruszylo bez problemu i przy usuwaniu rachunku wszystkie pozostale rekordy zostaly prawidlowo usuniete.

Ten post edytował Bonastick 3.12.2006, 14:04:26

[dr_bonzo]

Ok, teraz doczytalem wstep.

Czyli najprawdopodobniej przy usuwaniu rachunku dziala relacja do usuwania grup nalezacych do tego rachunku, ktore jednak nie moga byc usuniete, gdyz sa przyporzadkowane do nich operacje. Ale jesli by zdefiniowac kolejnosc dzialania relacji (przy usuwaniu rachunku najpierw usunac operacje do niego nalezace, adopiero potem grupy), wtedy wydaje mi sie, ze wszystko by zadzialalo.

I jaki bedzie (bylby -- bo tego sie chyba nie da wykonac) rezultat? Usunie ci operacje nalezace do danego rachunku, co pozwoli na usuniecie grup z tego rachunku, bo nie beda posiadaly juz zadnych operacji. Co da efekt usuniecia wszystkich grup z danego rachunku.

Chodzi o to, aby niemozliwe bylo usuniecie grup, do ktorych juz sa przyporzadkowane operacje

sam sobie zaprzeczasz.

Co chcesz otrzymac, co ma byc usuwalne a co nie i kiedy?


-------
edit:

Wyglada na to, ze klucze obce sa wykonywane w kolejnosci alfabetycznej. Nazwy dwoch klucz zazebiajacych sie, to: 'grupy_ibfk_1' i 'operacje_ibfk_1'.
Kiedy na poczatku tego pierwszego dopisalem "z" (zgrupy_ibfk_1), wszystko ruszylo bez problemu i przy usuwaniu rachunku wszystkie pozostale rekordy zostaly prawidlowo usuniete.

Dla mnie to hack. Skoro nie chcesz usuwania grup z operacjami to zabron tez usuwania rachunkow z grupami i usun pole id_rachunku z operacji.

[VGT]

sam sobie zaprzeczasz.

Co chcesz otrzymac, co ma byc usuwalne a co nie i kiedy?

Usuwanie rachunku to tak jakby czyszczenie calej bazy, tyle ze rachunkow ma byc wiele wiec truncate'a nie zastosuje
Natomiast usuwanie grupy to dzialanie w ramach uzytkowania danego rachunku, ktore ma byc zablokowane jesli grupa zawiera operacje (usuwac mozna tylko puste grupy).

No ale tak czy inaczej problem rozwiazany. Dzieki za zainteresowanie i pomoc

***EDIT
Zaproponowane przez Ciebie usuniecie relacji pomiedzy tabelami rachunki i operacje skomlikowaloby mi dwie rzeczy:
1. Jak wyciagnac wszystkie operacje nalezace do danego rachunku? Aktualnie to jest proste. W Twojej wersji musialbym ustalic id wszystkich grup nalezacych do danego rachunku a nastepnie wyciagnac wszystkie operacje nalezace do tych grup.
2. Jak usunac rachunek. Nie bede mogl usunac rachunku, bo bedzie mial grupy, nie bede mogl usunac grup, bo beda mialy operacje.

Jeszcze chcialem sie zapytac, dlaczego zmiana nazwy relacji to hack, tzn. czy takie jest Twoje zdanie czy tak jest w jakiejs specyfikacji (pytam z czystej ciekawosci, gdyz przeszukalem troche mysql.com pod tym katem ale nic ciekawego nie znalazlem).

Ten post edytował Bonastick 3.12.2006, 14:45:11

[dr_bonzo]

Jak wyciagnac wszystkie operacje nalezace do danego rachunku?

[SQL] pobierz, plaintext 
SELECT operacje.id, czy_co.tam_jeszcze_chcesz FROM rachunki JOIN grupy ON rachunek.id = grupy.rachunek_id JOIN operacje ON grupy.id = operacje.grupa_id WHERE rachunek.id = 555;
[SQL] pobierz, plaintext 

normalne podwojne zlaczenie, zupelnie prawidlowe rozwiazanie.

dlaczego zmiana nazwy relacji to hack

Nie tyle zmiania, co oparcie regul biznesowych twojej aplikacji na kolejnosci uwzgledniania foreign keyow przez baze. Jak pisalem: "Dla mnie...", wiec to moja opinia. Nie moglem znalezc niczego o kolejnosci rozpatrywania foreign keyow, nawet w manualu mysqla (http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html )

Jak usunac rachunek. Nie bede mogl usunac rachunku, bo bedzie mial grupy, nie bede mogl usunac grup, bo beda mialy operacje.

Zgadza sie. Ale czemu pozwalasz usuwac rachunek (razem z grupami i operacjami) a samych grup juz nie?

[VGT]

[SQL] pobierz, plaintext 
SELECT operacje.id, czy_co.tam_jeszcze_chcesz FROM rachunki JOIN grupy ON rachunek.id = grupy.rachunek_id JOIN operacje ON grupy.id = operacje.grupa_id WHERE rachunek.id = 555;
[SQL] pobierz, plaintext 

normalne podwojne zlaczenie, zupelnie prawidlowe rozwiazanie.

Zlaczenie 3 tabel vs. zapytanie do jednej tabeli w moim przypadku. Nie twierdze ze Twoja wersja bedzie wolna, gdyz nie moge tego powiedziec dopoki nie porobilbym testow na duzych tabelach, ale takie moje prywatne odczucie laika podpowiada ze lepiej i wygodniej uzywac mojej wersji, co jednak wcale nie musi oznaczac ze tak jest
Byc moze moja wersja kloci sie z zasadami projektowania, ale ksiazka na ten temat jest dopiero w planach

Nie tyle zmiania, co oparcie regul biznesowych twojej aplikacji na kolejnosci uwzgledniania foreign keyow przez baze. Jak pisalem: "Dla mnie...", wiec to moja opinia. Nie moglem znalezc niczego o kolejnosci rozpatrywania foreign keyow, nawet w manualu mysqla (http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html )

Tutaj tak, jak juz wspomnialem - takze szukalem informacji na ten temat i takze nic nie znalazlem co mnie dziwi. Rzowazajac moj przyklad, gdybym np tabele "grupy" nazwal "zarabistegrupy" i od nowa poprzydzielal klucze - w ogole nie byloby problemu i tego watku, gdyz relacje wykonalyby sie w takiej kolejnosci w jakiej od poczatku chcialem.
Skoro to ma znaczenie w takich przypadkach -wlanie dlatego spodziewalem sie znalezc cos na ten temat w dokumentacji.

Zgadza sie. Ale czemu pozwalasz usuwac rachunek (razem z grupami i operacjami) a samych grup juz nie?

To juz pisalem wczesniej. Usuniecie grupy to dzialanie w ramach uzytkowania rachunku. Aby zachowac spojnosc danych - mozna usunac tylko te ktore nie maja jeszcze zadnych operacji. Notamiast usuwanie rachunku to jego likwidacja. Moglbym po prostu usunac operacje wraz z ich opisami, usunac grupy i na koniec usunac rachunek, no ale teraz relacje zalatwiaja to za mnie. Efekt: mniejsze martwienie sie o spojnosc danych w bazie (przy 4 tabelach moze i ma to marne znaczenie, ale kiedy tabel bedzie kilkadziesiat, takie ulatwienia sa na wage zlota - juz przez takie projekty przechodzilem i pilnowanie takiego ogromu danych to masakra).

Rozpisalem sie, choc w zasadzie problem juz rozwiazywania nie wymaga, ale to zawsze podswiadomie nakrecam sie kiedy dyskusja sie robi ciekawa
Mam tylko nadzieje ze nie podpadam pod spamowanie.

Ten post edytował Bonastick 4.12.2006, 18:22:42

[dr_bonzo]

Mam tylko nadzieje ze nie podpadam pod spamowanie.

Z pewnoscia nie.

Usuniecie grupy to dzialanie w ramach uzytkowania rachunku. Aby zachowac spojnosc danych - mozna usunac tylko te ktore nie maja jeszcze zadnych operacji. Notamiast usuwanie rachunku to jego likwidacja.

Chyba mam rozwiazanie. Robisz REJECT miedzy operacjami a grupami, gupami a rachunkami. Usuwasz referencje od operacji do rachunku. I przy usuwaniu rachunku wywoluje sie trigger (ktoego musisz napisac ) ktory najpierw usunie wszystkie operacje, potem wszystkie grupy i na koniec rachunek

Ladnie pod wzgledem projektowym i poprawnie pod wzgledem regul biznesowych.
Co ty na to?

[VGT]

[...] wywoluje sie trigger (ktoego musisz napisac ) [...]

To pojecie u mnie jest aktualne na etapie: "a tak, slyszalem cos kiedys o tym"
Dotychczas wszystko co robilem to byly myisam'y z relacjami zdefiniowanymi tylko w mojej glowie i cala obrobka danych odbywala sie z poziomu php.
No ale trzeba isc do przodu i poznawac nowe rzeczy, wiec wszystko przede mna.

A co do rozwiazania:
zadowala Ciebie (poprawny projekt) i mnie (dzieki triggerowi zostanie zachowana prostota zarzadzania danymi). Zloty srodek, ktory tym bardziej skusi do poznania tych narzedzi