[mySql] auto associazione

[elmate]

ciao a tutti,
sto facedo un piccolo progetto didattico che dovrebbe avvicinarsi alla gestione di una distinta base e perciò 'entità "Articolo" dovrebbe alla fine contenere degli articoli composti da altri articoli.
in fase di progettazione nell'ER ho scelto l'auto associazione sull'entità Articolo.

presupposto che ogni articolo è identificato da una primary key "serial number"
l'altro attributo è "serial number padre" che, data la presenza della auto associazione, è una foreign key:

-- ----------------------------
-- Table structure for articolo
-- ----------------------------
CREATE TABLE `articolo` (
`serial number` varchar(20) NOT NULL default '',
`cliente privato` varchar(20) default NULL,
`cliente azienda` varchar(20) default NULL,
`marca` varchar(20) NOT NULL,
`modello` varchar(20) NOT NULL,
`descrizione` varchar(99) NOT NULL,
`serial number padre` varchar(20) default NULL,
`data vendita` date NOT NULL,
`data sostituzione` date default NULL,
`codice garanzia base` varchar(20) NOT NULL,
PRIMARY KEY (`serial number`),
KEY `cliente privato` (`cliente privato`),
KEY `cliente azienda` (`cliente azienda`),
KEY `marca` (`marca`),
KEY `modello` (`modello`),
KEY `codice garanzia base` (`codice garanzia base`),
KEY `serial number padre` (`serial number padre`),
CONSTRAINT `articolo_ibfk_1` FOREIGN KEY (`serial number padre`) REFERENCES `articolo` (`serial number`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `cliente azienda` FOREIGN KEY (`cliente azienda`) REFERENCES `azienda` (`codice cliente`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `cliente privato` FOREIGN KEY (`cliente privato`) REFERENCES `privato` (`codice cliente`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `codice garanzia base` FOREIGN KEY (`codice garanzia base`) REFERENCES `garanzia_base` (`codice garanzia`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `marca` FOREIGN KEY (`marca`) REFERENCES `tipo` (`marca`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `modello` FOREIGN KEY (`modello`) REFERENCES `tipo` (`modello`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 3072 kB; (`Composizione`) REFER `livitekassiste';

-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `articolo` VALUES ('11111', 'pri012345', null, 'nokia', 'n23', '', '12344', '0000-00-00', null, 'gb0001');
INSERT INTO `articolo` VALUES ('12344', 'pri012345', null, 'nokia', 'n23', '', null, '0000-00-00', null, 'gb0001');


come si può notare dai record, ho 2 articoli. uno è il padre (12344) mentre l'altro è il figlio (11111) che contiene il riferimento al padre tramite l'attributo "serial number padre".

arrivo al dunque:
se modifico la chiave del padre dovrebbe modificare il "serial number padre" nel figlio (la "gestione degli orfani" ON DELETE CASCADE ON UPDATE CASCADE)

però mi da errore (cosa che non succede se uso una foreign key utilizzando un'altra tabella)

Errore
query SQL:

UPDATE `livitekcc`.`articolo` SET `serial number` = '1234444' WHERE CONVERT( `articolo`.`serial number` USING utf8 ) = '12344' LIMIT 1

Messaggio di MySQL:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`livitekcc/articolo`, CONSTRAINT `articolo_ibfk_1` FOREIGN KEY (`serial number padre`) REFERENCES `articolo` (`serial number`) ON DELETE CASCADE ON UPDATE CASCADE)


come posso risolvere? c'è un trick da applicare alla tabella?
grazie!

[piero.mac]

per capire in modo chiaro bisognerebbe vedere tutta la struttura.

Desideravo solo farti notare un paio di cose.

la prima: non usare spazi nei nomi dei campi ma unisci le parole con underscore se vuoi fare le cose per bene. Es: serial_number_padre, non troverai mai un esempio di nome di campo con spazi all'interno. Lo spazio e' un carattere speciale.

la seconda: perche' fare due campi "cliente privato" e cliente azienda" ? essere privato oppure azienda dovrebbe essere un attributo di un campo "cliente".

In quanto all'errore una "foreign" key e' tale perche' ha il corrispettivo vincolo con un'altra tabella e non con se stessa.

[elmate]

ciao,

per gli spazi bianchi ok. vero. non l'ho ancora sistemato ma dovrei.

per gli attributi "cliente azienda", "cliente privato" ho fatto così in base allo schema ER in cui l'entità Cliente ha una specializzazione in privato e azienda. e dato che differivano per 5 o 6 attributi l'ho mantenuto separato.

nell'Articolo compilo "Cliente Azienda" oppure "Cliente privato".


per la questione della distinta base, foreign key sulla stessa tabella, concettualmente è spiegato nei libri come l'ER prevede l'auto associazione. solo che leggo online che mysql non lo sa fare. mentre oracle o sql server sì..


vabè cmq ho abbandonato l'idea della distinta base....se dovessi riprenderla mi creerei una tabella articoli e un'altra "righe articoli"....

OS baglio?

[elmate]

uhm ok mi hai comvinto riguardo i due attributi "privato" e "azienda"....
concettualmente, leggendo l'ER il cliente è stato specializzato (totale,esclusivo) in privato e azienda. ma poi queste due specializzazioni, anche se differiscono per 6 attributi, non si relazionano con altre entità.

quindi collasso verso l'alto, quindi verso "cliente" creando un'entità unica con tutti gli attributi di privato e azienda aggiungendo un booleano "is_privato".

e nell'entità "Articolo" meglio metto solo "codice cliente".


che ne dite?

buona giornata!