ik heb met PMA 2.9.2 deze interne relaties opgesteld. alle tabellen zijn met engine MyISAM
ik heb nu bv. :
3 id's in News_item
4 id's in News_category
en in News_itemcat (koppeltabel):
item cat
1 2
1 3
2 4
2 3
Nu mijn probleem: als ik een categorie wis in News_category (vb 2) dan blijft de relatie in de koppeltabel gewoon bestaan en krijg je corrupte data.
Hoe kan ik dit het best oplossen?
Engine MyISAM wil ik veranderen als het nodig is ;-)
(Als het echt niet kan, dan moet ik overal ingewikkelde query's gaan toepasssen maar ik hou het liever zo simpel mogelijk)
Het toepassen van relaties in MySQL doe je met de InnoDB engine, kijk hier maar eens bij de tutorials.
ALs je geen beschikking hebt over InnoDB, dan zal je het toch met query's moeten doen, maar dat is helemaal niet zo ingewikkeld:
Wanneer je een categorie wist, doe je dat aan de hand van een id die je (neem ik aan) meegeeft in een GET variabele.
Voor de koppeltabel betekend dat:
<?php
$sql = "DELETE FROM News_itemcat WHERE cat=".$_GET['cat'];
?>
[edit]
En voor het verwijderen, zal je inderdaad even alle newsitem_id's op moeten halen, dus dat doe je voordat je de bovenstaande DELETE query uitvoerd. Dit kan ook eenvoudig met een subquery:
<?php
$sql = "DELETE FROM News_item WHERE id IN(SELECT item FROM News_itemcat WHERE cat=".$_GET['cat'].")";
?>
[/edit]
Een DB kan het ook automatisch. Een InnoDB tabel kent twee 'triggers': on update en on delete. Beide spreken redelijk voor zich neem ik aan. De tabel kent voor beide triggers 4 acties: restrict, no action, cascade, set null
restrict en no action zijn in feite hetzelfde. Restrict zegt: het mag niet. No action wil het laten gebeuren, maar dat kan niet want dan zouden relaties kapot gaan. Dus in beide gevallen gebeurt niks en krijg je een error.
Set null doet heel duidelijk wat ie zegt (als het veld van de source tabel een NULL optie heeft!).
Cascade speelt het spelletje gewoon mee. Als je een rij verwijdert uit de reference tabel, verwijdert ie gewoon alle rijen uit de source tabel die erbij horen (ON DELETE) of hij update alle waarden in de source tabel als het id in de reference tabel wordt aangepast.
Het is veel zoek en probeer en denk werk om een goed InnoDB model te maken, maar als ie af is heb je een mooi systeempje. Subqueries, bah, nooit meer nodig. Zet je ON DELETE op CASCADE en alle bijbehorende rijen uit welke tabel dan ook worden automatisch verwijderd.
Mocht je er, na het lezen van dit artikel, niet uitkomen: Stuur me ff een PB'tje, dan kan ik evt. het artikel aanpassen als dat nodig. InnoDB is inderdaad wat je nodig hebt.
oke bedankt allen, ik ga even op onderzoek via het artikel ...
ik neem idd de optie innodb ;)
--- EDIT ---
Ik ben er nog niet helemaal uit. Ik heb nu deze code:
--
-- Tabel structuur voor tabel 'News_category'
--
CREATE TABLE News_category (
id int(11) NOT NULL auto_increment,
naam varchar(255) NOT NULL,
datum datetime NOT NULL,
edit timestamp NULL default NULL on update CURRENT_TIMESTAMP,
ip varchar(15) NOT NULL default '000.000.000.000',
PRIMARY KEY (id),
UNIQUE KEY naam (naam)
) ENGINE=INNODB;
-- --------------------------------------------------------
--
-- Tabel structuur voor tabel 'News_comment'
--
CREATE TABLE News_comment (
id int(11) NOT NULL auto_increment,
titel varchar(255) NOT NULL,
bericht text NOT NULL,
datum datetime NOT NULL,
item int(11) NOT NULL,
auteur int(11) NOT NULL,
edit timestamp NULL default NULL on update CURRENT_TIMESTAMP,
ip varchar(15) NOT NULL default '000.000.000.000',
PRIMARY KEY (id)
) ENGINE=INNODB;
-- --------------------------------------------------------
--
-- Tabel structuur voor tabel 'News_item'
--
CREATE TABLE News_item (
id int(11) NOT NULL auto_increment,
titel varchar(255) NOT NULL,
bericht text NOT NULL,
datum datetime NOT NULL,
auteur int(11) NOT NULL,
edit timestamp NULL default NULL on update CURRENT_TIMESTAMP,
ip varchar(15) NOT NULL default '000.000.000.000',
PRIMARY KEY (id)
) ENGINE=INNODB;
-- --------------------------------------------------------
--
-- Tabel structuur voor tabel 'News_itemcat'
--
CREATE TABLE News_itemcat (
item int(11) NOT NULL,
categorie int(11) NOT NULL,
UNIQUE KEY itemcategorie (item,categorie)
) ENGINE=INNODB COMMENT='Koppeltabel item <-> categorie';
als er een categorie verwijderd word, dan moeten alle koppelitems wissen, maar niet de bijhorende items (omdat een item meerdere categoriën kan bevatten)
als er een categorie verwijderd word, dan moeten alle koppelitems wissen
Dat betekend dus dat je in je koppeltabel een foreign key hebt die wijst naar categorie, met als eigenschap "ON DELETE CASCADE". Je wilt dan uiteraard niet dat de artikelen verwijderd worden, maar dat gebeurt ook niet als je het model goed opstelt:
CREATE TABLE News_itemcat (
item int(11) NOT NULL,
categorie int(11) NOT NULL,
UNIQUE KEY itemcategorie (item,categorie)
FOREIGN KEY(item) REFERENCES News_item(id) ON DELETE CASCADE,
FOREIGN KEY(categorie) REFERENCES News_category(id) ON DELETE CASCADE
) ENGINE=INNODB COMMENT='Koppeltabel item <-> categorie';
(ik heb deze SQL niet geprobeerd). Het idee is als volgt:
- Leg een relatie op "item", deze wijst naar News_item->id. Als er een News_item wordt verwijderd, wordt deze ook automatisch uit de koppeltabel verwijderd. Je dwingt op deze manier af dat News_items in je koppeltabel ook echt bestaan.
- Leg een relatie op "categorie", deze verwijst naar News_category->id. Als er een News_categorie wordt verwijderd, wordt deze ook automatisch uit de koppeltabel gegooid. News_items worden hierdoor niet geraakt.
nu nog 1 vraagje, wat zou ik best doen met nieuwsberichten die van een user zijn die verwijderd word?
Normaal gesproken verwijder je een user niet maar de-activeer je hem. Als een user echt verwijderd wordt horen zijn berichten ook verwijderd te worden, dit kan echter inconsistente gegevens opleveren (denk bijv. aan een forum, van threads waarin de gebruiker actief was). Overweging: userid op NULL zetten, user de-activeren, alles verwijderen....de keuze is echt afhankelijk van je systeem :)
