[MYSQL] chiavi esterne e update

[coteaz]

salve, volevo qualche delucidazione sulle chiavi esterne, le uso gia, e mi trovo bene ma volevo implementare una funzione che, forse, non è possibile ottenere in SQL

ho queste due tabelle

FORNITORE
id
nome
ecc

PRODOTTO
id
id_fornitore
nome
ecc

aggiungendo una chiave esterna sul campo PRODOTTO.id_fornitore -> FORNITORE.id e settando on delete cascade, on update no action posso far in modo che cancellando un fornitore, vengano cancellati in automatico, e senza scrivere codice, tutti i prodotti associati a quel fornitore.

funzione molto molto comoda, quanto pericolosa... se ho 100 prodotti associati ad un fornitore e cancello per sbaglio il fornitore, perderò tutti e 100 i prodotti.

a questo punto per avere uno storico, e per evitare casini, avevo pensato di effettuare delle cancellazioni logiche dal DB inserendo un campo enum attivo nelle tabelle FORNITORE e PRODOTTO con valori 'si','no'.

in caso di cancellazione di un fornitore (ovviamente senza la chiave esterna) il campo attivo va settato su 'no' e tutti i prodotti restano nel db.

FATTA QUESTA PREMESSA, LA MIA DOMANDA è QUESTA
in SQL posso creare una chiave esterna che mi fa questo?

se aggiorno il campo FORNITORE.attivo e il valore è uguale a 'no' imposta tutti i campi PRODOTTO.attivo su 'no' dove FORNITORE.id è uguale a PRODOTTO.id_fornitore...

in php la cosa sarebbe molto facile, ma posso farlo fare ad una chiave esterna?
nelle chiavi esterne c'è anche il valore ON UPDATE, si possono aggiungere delle condizioni

spero di essere stato chiaro, aspetto un vostro riscontro.

GRAZIE

[gianf_tarantino]

No le chiavi esterne non permettono di modificare valori dei campi che non siano coinvolti, appunto, nella relazione esterna. Tutt'al + potresti utilizzare dei trigger.

Il consiglio che ti dò è questo. Ti crei delle tabelle di recupero dati, tipo dati_fornitori e dati_prodotti con gli stessi campi delle tabelle fornitore e prodotto.

Quando elimini un fornitore, prima di eliminarlo prendi i suoi dati e quelli dei prodotti che verranno eliminati e li metti nella tabella dati_fornitori e dati_prodotti.

Poi elimini il fornitore e grazie alla relazione esterna verranno eliminati anche i prodotti.

Questo perché? Perché se tu implementi il tuo metodo con un campo enumerativo, col passare del tempo le tue 2 tabelle diverranno troppo grandi e potrai avere problemi di gestione delle stesse e di efficienza. Col mio metodo, invece, tu i dati eliminati li vai a mettere in tabelle di "backup" da cui poi li puoi recuperare quando ti servono.
è ovvio poi che ad esempio puoi decidere che ogni tot mesi svuoti dalle tabelle di backup quei dati che stanno lì da troppo tempo e che ormai è sicuro che non ti interessa + recuperare.

[coteaz]

grazie per il consiglio, cmq io son riuscito, con un po di prove, ad ottenere qualcosa

se cambio il valore di attivo nel campo padre cambia anche il valore di attivo nel campo figlio

posto un po di SQL

-- Struttura della tabella `fornitore`
--

CREATE TABLE IF NOT EXISTS `fornitore` (
  `FORNITORE_id` bigint(10) NOT NULL auto_increment,
  `FORNITORE_nome` varchar(100) NOT NULL,
  `FORNITORE_attivo` varchar(100) NOT NULL,
  PRIMARY KEY  (`FORNITORE_id`),
  KEY `FORNITORE_id` (`FORNITORE_id`,`FORNITORE_attivo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `prodotto`
--

CREATE TABLE IF NOT EXISTS `prodotto` (
  `PRODOTTO_id` bigint(10) NOT NULL auto_increment,
  `FORNITORE_id` bigint(10) NOT NULL,
  `PRODOTTO_nome` varchar(100) NOT NULL,
  `PRODOTTO_attivo` varchar(100) NOT NULL,
  PRIMARY KEY  (`PRODOTTO_id`),
  KEY `FORNITORE_id` (`FORNITORE_id`,`PRODOTTO_attivo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

ALTER TABLE `prodotto`
  ADD CONSTRAINT `prodotto_fk` FOREIGN KEY (`FORNITORE_id`, `PRODOTTO_attivo`) REFERENCES `fornitore` (`FORNITORE_id`, `FORNITORE_attivo`) ON UPDATE CASCADE;

con questa chiave esterna doppia ho messo in relazione sia fornitore.FORNITORE_id->prodotto.FORNITORE_id che fornitore.FORNITORE_attivo-> prodotto.PRODOTTO_attivo...

effettuando una cancellazione logica e settando il campo fornitore.FORNITORE_attivo = no, tutti i campi prodotto.PRODOTTO_attivo andranno su no (dove fornitore.FORNITORE_id = prodotto.FORNITORE_id)

E FINO A QUI CI SIAMO...

solo che... se voglio cancellare un prodotto (quindi un capo dalla tabella figlio) impostando il valore di attivo su NO l'sql mi darà errore per via della chiave... come posso ovviare a questo inconveniente?

grazie