MySQL - problem z kluczami obcymi itp.

Question

Witajcie,

Piszę tutaj pierwszy raz, choć wiele razy czytałem posty innych osób :)

Teraz sam przychodzę z problemem, którego nie jestem wstanie sam zrozumieć ani poprawić.

Jestem samoukiem jeżeli chodzi o PHP i MySQL - ostatnio porwałem się na stworzenie własnej małej "aplikacji" dla firmy w której pracuje. Chodzi o przyjmowanie dostaw (data, rachunki, wartości, przeliczenia).

No i tak - wprowadzam dane do MySQL, do trzech tabel (dostawy, sendungsnumer, rachunki): w dostawach mam id_dostaw, date dostaw, kurs dostawy; sendungsnumery (taki nr nadrachunek); i numery rachunków z kwotami. Mam również ustawione relacje, ale pewnie nie najlepiej, co wykazuje mój problem.

Wszystko pięknie, jednak napotkałem problem w aplikacji.

Dostawa A (11.04.20, 4,3512):

111 

1 - 45,50 €

Dostawa B (15.04.20, 4,4151):

111

1 - 176 €

Problem pojawia się w momencie, gdy usuniemy np. rachunek nr 1 z dostawy A, wtedy w dostawie A zostaje nam sam "nadrachunek" 111. Jednak nie jestem go wstanie usunąć, ze względu na to, że widzi pod sobą rachunek 1 - z tym, że on jest przypisany do innej dostawy... nie jestem wstanie rozgryźć tego problemu. Jak to zrobić żeby było dobrze. Najprawdopodobniej lepiej zaprojektować bazę danych, jednak nie wiem jak.

Mam nadzieję, że w miarę zrozumiale napisałem o co chodzi.

Czy ktoś jest wstanie pomóc?

Comments

Wstaw polecenia użyte do stworzenia tablel.

Jeżeli nie masz tego pod ręką wywołaj polecenie.

SHOW CREATE TABLE nazwa_tabeli

 

---

CREATE TABLE `dostawa` (
 `id` tinyint(4) NOT NULL AUTO_INCREMENT,
 `id_dostawy` tinyint(4) NOT NULL,
 `data_dostawy` date NOT NULL,
 `kurs_euro` decimal(5,4) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id_dostawy` (`id_dostawy`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

CREATE TABLE `rachunki` (
 `id` tinyint(4) NOT NULL AUTO_INCREMENT,
 `id_dostawy` tinyint(11) NOT NULL,
 `sendungsnumer` int(11) NOT NULL,
 `rachunek` int(11) NOT NULL,
 `kwota_rachunku` decimal(11,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `sendungsnumer` (`sendungsnumer`),
 KEY `id_dostawy` (`id_dostawy`),
 CONSTRAINT `rachunki_ibfk_1` FOREIGN KEY (`sendungsnumer`) REFERENCES `sendungsnumery` (`sendungsnumer`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8

CREATE TABLE `sendungsnumery` (
 `id` tinyint(4) NOT NULL AUTO_INCREMENT,
 `id_dostawy` tinyint(4) NOT NULL,
 `sendungsnumer` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `id_dostawy` (`id_dostawy`),
 KEY `sendungsnumer` (`sendungsnumer`) USING BTREE,
 CONSTRAINT `sendungsnumery_ibfk_1` FOREIGN KEY (`id_dostawy`) REFERENCES `dostawa` (`id_dostawy`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8

Tak to wygląda... 

---

Wg tego schematu usunięcie wpisu z tabeli sendungsnumery spowoduje usunięcie wszystkich powiązanych z nim wierszy z tabeli rachunki.
Dane z tabeli dostawa mogą zostać usunięte tylko wtedy, gdy nie mają przypisanych żadnych wierszy w tabeli sendungsnumery.

Nie widzę gdzie tu może być problem.

Pokaż jakieś przykładowe dane i polecenia które powodują błąd.

---

Oczywiście masz racje, taki był zamysł. 
(Mała poprawka - przy rachunkach miało być ON UPDATE RESTRICT, ON DELETE RESTRICT, wtedy nie można usunąć sendungsnumera jeżeli istnieje pod nim rachunek).
Jednak problem pojawia się, gdy stworzę dwie różne dostawy i przypadkiem okaże się, że wprowadze dwa takie same sendungsnumery i dodam dwa takie same rachunki w obu dostawach. Wtedy przy usunięciu jednego z rachunków z jednej dostawy, powoduje że nie jestem wstanie usunąć tego sendungsnumeru, gdyż istnieje rachunek „przypisany” pod tym samym sendungsnumerem ale w innej dostawie. Teraz nie wiem czy muszę wymusić żeby oba te numery były unikalne dla całości, czy istnieje jakiś sposób żeby to napisać inaczej... 

---

Nie zauważyłem tego.
Tabela rachunki powinna być powiązana do unikalnego indeksu w tabeli sendungsnumery (najlepiej do pola id).

Tak samo dla połączenia tabel sendungsnumery i dostawy.

Poczytaj o postaci normalnej bazy danych.

---

Dzięki za podpowiedź. Jutro postaram się z tym zmierzyć, w razie czego się jeszcze odezwę :)