postgresql: chiavi primarie e secondarie, inserire record su piu` tabelle contemporaneamente
filip...@virgilio.it
ho due tabelle di questo tipo
tabella 1
ID1, cognome, nome
tabella 2
ID2, reparto
ID1 e` chiave primaria, ID2 chiave secondaria relazionata a ID1. Quando
inserisco un record in tabella 1 vorrei inserire anche il
corrispondente in tabella 2; in ID2 vorrei mettere lo stesso ID1 di
tabella 1. Il problema e` che ID1 e` autoincrementale per cui non lo
conosco a priori. E` possibile fare una query che mi unisca le due
tabelle e poi inserire il record nella query? Se si, come si fa? Se no,
come faccio a gestire le due tabelle con la chiave in comune?
Ciao e grazie,
Filippo
The man with two watches
> ho due tabelle di questo tipo
> tabella 1
> ID1, cognome, nome
>
> tabella 2
> ID2, reparto
>
> ID1 e` chiave primaria, ID2 chiave secondaria relazionata a ID1.
La cosa principale che dovresti fare è rivedere il modo in cui
ti approcci al db.
E' sbalorditivo vedere quanti danni facciano questi id; la pratica
è talmente diffusa che attualmente lo sforzo maggiore per chi
entra nel settore è *disimparare*.
Un id autoincrementante non potrà essere *MAI* una chiave primaria;
un id è un elemento FISICO del motore di database e non un elemento
logico dello schema concettuale che il db dovrebbe modellare; in
questo modo identifichi un _record_, non una riga (persona in questo
caso).
Quello che tenti di fare è implementare una cardinalità 1:1-n su
un attributo: questa è più una modellazione fisica che altro (dico di
più in seguito).
Una chiave ha campo di validità solo all'interno di una stessa tabella.
Il termine "chiave secondaria" è scorretto in questo contesto;
dovresti parlare di _vincolo_ Primary Key/Foreign Key; ID2 non è
una chiave (considera ad. esempio il principio di univocità).
Si parla di Primary Key/Alternate key solo all'interno di una stessa
tabella. Inoltre piuttosto che relazionata io preferisco il termine
"riferita", dato che la relazione è altra cosa.
> Quando
> inserisco un record in tabella 1 vorrei inserire anche il
> corrispondente in tabella 2; in ID2 vorrei mettere lo stesso ID1 di
> tabella 1. Il problema e` che ID1 e` autoincrementale per cui non lo
> conosco a priori.
Il problema è che non hai identificato correttamente le chiavi
primarie, ed il fatto che non sai che valore mettere nella seconda
tabella è solo uno dei problemi che avrai, di cui il più grande è
che non c'è integrità logica dei dati.
Infatti tu hai costruito l'integrità FISICA dei record.
Considera questo esempio basato sulle tue specifiche:
Persone
-------
1 Rossi Mario
2 Bianchi Carlo
3 Verdi Gino
Assegnazioni
------------
1 Contabilità
2 Customer care
2 Contabilità
3 Manutenzione
3 Magazzinaggio
3 Speditura
Immagina di avere colonne nullabili di "svuotare" le tabelle di tutti
i dati meno che delle chiavi.
Persone
-------
1
2
3
Assegnazioni
------------
1
2
2
3
3
3
A vista riconosci che la struttura delle tabelle sono le stesse; pure
i select, update ed insert continuano a funzionare regolarmente.
Riesci a vedere come hai costruito l'integrità FISICA delle tabelle,
ma non quella dei dati?
> E` possibile fare una query che mi unisca le due
> tabelle e poi inserire il record nella query? Se si, come si fa? Se no,
> come faccio a gestire le due tabelle con la chiave in comune?
Non è in "comune", il vincolo pk/fk è solo un vincolo di integrità;
le tabelle potrebbero tranquillamente avere vita completamente separata
e distinta incluso il stare in diversi database (premesso appunto che
indentifichi correttamente le chiavi).
> Ciao e grazie,
> Filippo
Buon lavoro.
Marcello
> E' sbalorditivo vedere quanti danni facciano questi id; la pratica
> è talmente diffusa che attualmente lo sforzo maggiore per chi
> entra nel settore è *disimparare*.
Ciao,
Cioè?? Sei contrario alle chiavi surrogate? E perchè mai?
> Un id autoincrementante non potrà essere *MAI* una chiave primaria;
Ancora, cioè?? e perchè mi??
> un id è un elemento FISICO del motore di database e non un elemento
> logico dello schema concettuale che il db dovrebbe modellare; in
> questo modo identifichi un _record_, non una riga (persona in questo
> caso).
Ok, e quindi?
> Quello che tenti di fare è implementare una cardinalità 1:1-n su
> un attributo: questa è più una modellazione fisica che altro (dico di
> più in seguito).
Quindi se la chiave naturale fosse Nome,Cognome,Indirizzo, tu
riporteresti tutti campi in tutte le tabelle relazionate? Con quali
vantaggi??
> Una chiave ha campo di validità solo all'interno di una stessa tabella.
Cosa significa? :-D
> Il termine "chiave secondaria" è scorretto in questo contesto;
> dovresti parlare di _vincolo_ Primary Key/Foreign Key;
> ID2 non è
> una chiave (considera ad. esempio il principio di univocità).
...mmm... Se non ho capito male filippo punta proprio a una relazione
1-1 e quindi ID2 deve essere univoco e magari anche pk...
> Si parla di Primary Key/Alternate key solo all'interno di una stessa
> tabella. Inoltre piuttosto che relazionata io preferisco il termine
> "riferita", dato che la relazione è altra cosa.
Si ok, si tratta in questo caso, mi pare di capire, di un
partizionamento verticale, ci si può chiedere piuttosto se sia necessario.
> Il problema è che non hai identificato correttamente le chiavi
> primarie, ed il fatto che non sai che valore mettere nella seconda
> tabella è solo uno dei problemi che avrai, di cui il più grande è
> che non c'è integrità logica dei dati.
ma perchè???
> Immagina di avere colonne nullabili di "svuotare" le tabelle di tutti
> i dati meno che delle chiavi.
Certo, se sono tutte colonne nullabili non c'è integrità dei dati, ma
questo cosa dimostrerebbe? E cosa centra l'autoincrementante? In
generale se i dati sono definiti in modo scorretto non ne è garantita
l'integrità, ma non capisco che nesso ci sia con il caso in discussione.
> A vista riconosci che la struttura delle tabelle sono le stesse; pure
> i select, update ed insert continuano a funzionare regolarmente.
> Riesci a vedere come hai costruito l'integrità FISICA delle tabelle,
> ma non quella dei dati?
Ma filippo non ha mai specificato nulla sulle proprietà delle varie colonne!
> Non è in "comune", il vincolo pk/fk è solo un vincolo di integrità;
> le tabelle potrebbero tranquillamente avere vita completamente separata
> e distinta incluso il stare in diversi database (premesso appunto che
> indentifichi correttamente le chiavi).
Non in una relazione 1-1
> Buon lavoro.
marc.
filippo
tabella anagrafica
contiene tutti anagrafici delle persone. Ha il campo ID che indntifica
univocamente le persone (risolvo il problema degli omonimi)
tabella 2
questa tabella associa alle persone i servizi che utilizzano:
- ogni riga associa un ID di anagrafica ad un servizio
- ogni persona puo` avere piu` servizi attivi (e quindi piu` righe in
tabella 2)
- ci possono essere persone in anagrafica che non compaioni nella
tabella 2 (persone senza servizi associati)
- non ci possono essere servizi in tabella due senza persona associata
(record di tabella 2 senza riferimento alla persona)
il campo ID della tabella 1 e` definito come chiave primaria e voglio
utilizzarlo anche come ID univoco di persona da associare nella tabella
2 (questo per evitare il gia` citato problemi degli omonimi)
nel caso volessi simultaneamente creare una nuova persona in tabella 1
e assegnarle un servizio, ho bisogno di conoscere il valore di ID di
quella persona da inserire nel campo ID della tabella 2. Come posso
farlo?
Ciao e grazie,
Filippo
il campo ID della tabella 2 (che viene creato automaticamente dal
database ad ogni INSERT in tabella) va` associato a
Marcello
> ops, non ho capito molto. La mia esigenza e` la seguente:
> tabella anagrafica
> contiene tutti anagrafici delle persone. Ha il campo ID che indntifica
> univocamente le persone (risolvo il problema degli omonimi)
> tabella 2
> questa tabella associa alle persone i servizi che utilizzano:
> - ogni riga associa un ID di anagrafica ad un servizio
> - ogni persona puo` avere piu` servizi attivi (e quindi piu` righe in
> tabella 2)
> - ci possono essere persone in anagrafica che non compaioni nella
> tabella 2 (persone senza servizi associati)
> - non ci possono essere servizi in tabella due senza persona associata
> (record di tabella 2 senza riferimento alla persona)
Ciao filippo,
si tratta di una normalissima relazione 1-n.
> nel caso volessi simultaneamente creare una nuova persona in tabella 1
> e assegnarle un servizio, ho bisogno di conoscere il valore di ID di
> quella persona da inserire nel campo ID della tabella 2. Come posso
> farlo?
A seconda del motore che utilizzi [sql server, my sql, oracle...]
esistono vari strumenti per recuperare l'ultimo id inserito e quindi
procedere con l'inserimento nella tabella in relzione.
> Ciao e grazie,
> Filippo
marc.
filippo
quello giusto.
Essendo l'ambiente multiutente, come posso essere sicuro che non
succeda questo:
- aggiungo un record sulla tabella 1
- un altro utente fa la stessa cosa
- faccio una query che mi trovi l'ultimo inserito
- aggiungo l'nformazione sulla tabella 2, sbagliata perche` e` riferita
non al mio INSERT ma a quello dell'altro utente
come posso evitare cio?
Ciao e grazie,
Filippo
Alessandro Pellizzari
> Essendo l'ambiente multiutente, come posso essere sicuro che non
> succeda questo:
Apri una sessione (dipende dal linguaggio come viene vista. In Python
hai un cursore), fai l'insert e richiedi al cursore l'ultimo id. Lui ti
tornera` il suo ultimo id, non quello di altri utenti che possono
essersi collegati nel frattempo.
Bye.
--
Alessandro Pellizzari
filippo
e` proprio quello che mi serve! Io uso postgresql con perl, ma non ho
mai sentito nulla di cio`. Mi sono perso qualcosa?
Ciao e grazie,
Filippo
The man with two watches
>> The man with two watches ha scritto:
>> E' sbalorditivo vedere quanti danni facciano questi id; la pratica
>> è talmente diffusa che attualmente lo sforzo maggiore per chi
>> entra nel settore è *disimparare*.
> Ciao,
> Cioè?? Sei contrario alle chiavi surrogate? E perchè mai?
Ciao Marcello.
Sono contrario all'uso cieco e sistematico degli ID autoincrementanti
_come_chiavi_primarie_. Il concetto di chiave primaria è estremamente
importante per l'integrità dei dati e per tutto ciò che ne
consegue.
>> Un id autoincrementante non potrà essere *MAI* una chiave primaria;
> Ancora, cioè?? e perchè mi??
Googla i miei post passati; i motivi sono talmente tanti che dopo mesi
che scrivo non ho ancora ripetuto due volte lo stesso argomento!! Ci
sono tonnellate di buoni motivi, di cui uno ed il più fondamentale
segue adesso.
>> un id è un elemento FISICO del motore di database e non un elemento
>> logico dello schema concettuale che il db dovrebbe modellare; in
>> questo modo identifichi un _record_, non una riga (persona in questo
>> caso).
> Ok, e quindi?
Uh, ti sei distratto? In una conversazione sull'uso errato degli id,
abbiamo appena convenuto che:
1) l'ID è un elemento fisico
2) uno schema db è logico
>> Quello che tenti di fare è implementare una cardinalità 1:1-n su
>> un attributo: questa è più una modellazione fisica che altro (dico
>> di più in seguito).
> Quindi se la chiave naturale fosse Nome,Cognome,Indirizzo, tu
> riporteresti tutti campi in tutte le tabelle relazionate? Con quali
> vantaggi??
Ormai l'uso sistematico degli id è talmente radicato che io mi sto
trovando nella situazione di DOVER GIUSTIFICARE PERCHE' MAI SEGUO
LA TEORIA RELAZIONALE!!! Ti rendi conto che questa domanda suona come
"Perché fai le cose per bene?".
Scusami, ma è chi devia dalla strada principale che dovrebbe
spiegare perché lo fa, e non viceversa!
>> Una chiave ha campo di validità solo all'interno di una stessa tabella.
> Cosa significa? :-D
Lo spiego in tutto il resto del paragrafo di cui tu hai citato solo la
prima frase. Detta semplice: nel nostro esempio, il fatto che ID2 si
riferisca alla chiave ID1 non la rende una chiave; infatti è un
attributo.
>> Il termine "chiave secondaria" è scorretto in questo contesto;
>> dovresti parlare di _vincolo_ Primary Key/Foreign Key; ID2 non è
>> una chiave (considera ad. esempio il principio di univocità).
> ...mmm... Se non ho capito male filippo punta proprio a una relazione
> 1-1 e quindi ID2 deve essere univoco e magari anche pk...
Infatti hai capito male: guarda cosa scrive: "- ogni persona puo`
avere piu` servizi attivi (e quindi piu` righe in tabella 2)".
>> Il problema è che non hai identificato correttamente le chiavi
>> primarie, ed il fatto che non sai che valore mettere nella seconda
>> tabella è solo uno dei problemi che avrai, di cui il più grande è
>> che non c'è integrità logica dei dati.
> ma perchè???
Come perché? Non ti preoccupa neanche un po' che l'op sta operando
con un database SENZA CHIAVI VALIDE!?!
>> Immagina di avere colonne nullabili di "svuotare" le tabelle di tutti
>> i dati meno che delle chiavi.
> Certo, se sono tutte colonne nullabili non c'è integrità dei dati, ma
> questo cosa dimostrerebbe? E cosa centra l'autoincrementante? In
> generale se i dati sono definiti in modo scorretto non ne è garantita
> l'integrità, ma non capisco che nesso ci sia con il caso in discussione.
Questo esempio serviva a capire che quelle chiavi id non identificano
un'istanza dell'entità, ma un'altra cosa.
Ho detto di svuotare la tabella, ma potevo dire anche "copri i dati
tranne le chiavi primarie con una mano, e rifletti su cosa identificano".
>> A vista riconosci che la struttura delle tabelle sono le stesse; pure
>> i select, update ed insert continuano a funzionare regolarmente.
>> Riesci a vedere come hai costruito l'integrità FISICA delle tabelle,
>> ma non quella dei dati?
> Ma filippo non ha mai specificato nulla sulle proprietà delle varie
> colonne!
Si l'ha fatto, ed è l'argomento del thread: chiavi e vincoli, ed è
abbastanza per capire quali errori fondamentali sta commettendo.
>> Non è in "comune", il vincolo pk/fk è solo un vincolo di integrità;
>> le tabelle potrebbero tranquillamente avere vita completamente
>> separata e distinta incluso il stare in diversi database (premesso
>> appunto che indentifichi correttamente le chiavi).
> Non in una relazione 1-1
Casomai, non in un vincolo pk/fk.
Ribadisco ed amplio: l'identificazione corretta delle chiavi permette
a tabelle in database diversi di identificare correttamente la stessa
entità.
O credi che, ad esempio tra amministrazioni diverse di uno stato, ci
sia sempre una connessione attiva per prelevare il valore dell'ultimo
ID inserito in una data tabella, altrimenti non si può fare niente
se una persona si presenta ad uno sportello?
Ciao
The man with two watches
> ops, non ho capito molto. La mia esigenza e` la seguente:
> tabella anagrafica
> contiene tutti anagrafici delle persone. Ha il campo ID che indntifica
> univocamente le persone (risolvo il problema degli omonimi)
Se prima avevi come chiave cognome-nome, identificazione errata della
pk, adesso hai tolto ogni chiave direttamente. Ti rendi conto che il
tuo db permette:
1 Rossi Mario
2 Rossi Mario
[...]
9999999999 Rossi Mario
Andrai a finire come il tuo collega di un paio di settimane fa, che
su questo ng, chiedeva lumi su come capire se due "record" sono
duplicati. Non vedi come quell'ID è totalmente farlocco?
> tabella 2
> questa tabella associa alle persone i servizi che utilizzano:
> - ogni riga associa un ID di anagrafica ad un servizio
No, non associa alle persone, associa ai RECORD, che è una cosa
drammaticamente diversa.
> nel caso volessi simultaneamente creare una nuova persona in tabella 1
> e assegnarle un servizio, ho bisogno di conoscere il valore di ID di
> quella persona da inserire nel campo ID della tabella 2. Come posso
> farlo?
Il valore della pk lo devi già sapere, altrimenti come fai a sapere
se hai già inserito una data persona oppure no? Un doppio inserimento
di una PK fa scattare un errore, invece cogli ID puoi inserire la
stessa persona fino all'esaurimento della memoria!!
The man with two watches
> A seconda del motore che utilizzi [sql server, my sql, oracle...]
> esistono vari strumenti per recuperare l'ultimo id inserito e
> quindi procedere con l'inserimento nella tabella in relzione.
Per amor di ragionamento, rispondi a questo:
Se faccio un INSERT di n righe nella tabella1, quanti ID di ritorno
avrei?
(avviso: trappola in agguato) :->
filippo
# avvia la transazione
$dbh->do('begin');
# aggiorna contatore id clienti
$sth = $dbh->prepare("SELECT nextval('contatore_id_clienti')");
$sth->execute;
$new_id = ($sth->fetchrow_array)[0];
e poi utilizzo il valore $new_id nelle query successive
# commit
$dbh->do('commit');
il tuo discorso e` interessante, cerchero` di capire se avere una
chiave cognome,nome,datanascita e` piu` efficiente che usare un id
univoco.
Ciao e grazie,
Filippo
Alessandro Pellizzari
> >Apri una sessione (dipende dal linguaggio come viene vista. In Python
>
> e` proprio quello che mi serve! Io uso postgresql con perl, ma non ho
> mai sentito nulla di cio`. Mi sono perso qualcosa?
Mi spiace, ma non conosco perl.
Postgres lo uso in Python e PHP.
Bye.
--
Alessandro Pellizzari
hk3project
> il tuo discorso e` interessante, cerchero` di capire se avere una
> chiave cognome,nome,datanascita e` piu` efficiente che usare un id
> univoco.
non sono certo un guru ma credo ti convenga usare il codice fiscale
se hai tutti i dati per calcolarlo
hk3project
> Davide
>
il codice fiscale identifica una sola persona, perche' non
dovrebbe essere univoco? (giusto per curiosita' e cultura personale)
Alessandro Pellizzari
> Davide Bianchi ha scritto:
> > On 2006-05-16, hk3project <hk3pr...@gmail.com> wrote:
> >> non sono certo un guru ma credo ti convenga usare il codice fiscale
> >> se hai tutti i dati per calcolarlo
> >
> > Nessuno dei due e' univoco.
> il codice fiscale identifica una sola persona, perche' non
> dovrebbe essere univoco? (giusto per curiosita' e cultura personale)
Il codice fiscale e` univoco solo per l'Italia e solo se hai l'originale
rilasciato dal Ministero delle Finanze.
I codici fiscali calcolati non sono univoci, perche` ci sono collisioni che
vengono risolte, appunto, dal Ministero in fase di assegnazione, cambiando
uno dei caratteri.
Bye.
--
Alessandro Pellizzari
hk3project
> Il giorno mar, 16/05/2006 alle 16.38 +0200, hk3project ha scritto:
>> il codice fiscale identifica una sola persona, perche' non
>> dovrebbe essere univoco? (giusto per curiosita' e cultura personale)
>
> vengono risolte, appunto, dal Ministero in fase di assegnazione, cambiando
> uno dei caratteri.
perfetto, grazie