[MySQL] chiavi non autoincrement, foreign key, etc.

[skidx]

'sera a tutti, alcune questioni sul MySQL.

A) Per alcune ragioni dovrei usare una tabella la cui chiave primaria è composta da più campi numerici separati, quindi non posso usare un campo autoincrement.

La soluzione che ho pensato per generare la chiave sarebbe:
SELECT MAX(id) FROM tabella FOR UPDATE;
INSERT INTO TABELLA id = $id+1 ...
(il tutto dentro una transazione)

Nel manuale parla anche di questo metodo:
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();

Secondo voi si può usare qualcosa del genere anche per l'insert, in maniera più conveniente rispetto alla mia idea o esiste un altro metodo alternativo?

B) Ho necessità di gestire un numero limitato di record (diciamo 256) contemporanei.
Possono cioè esserci contemporaneamente massimo 256 record di quel tipo. Ok, uso un campo tinyint unsigned, e lo incremento.
Arrivati a 255, che si fa?
1) Continuo ad incrementare sovrascrivendo l'eventuale record con valore 1, e gestendo quindi questa sovrascrittura di conseguenza.
2) cerco uno spazio vuoto tra i 255, se lo trovo uso quello, altrimenti il "set" è pieno.

Cosa mi suggerite?
Eventuali altre ipotesi?

C)Nella tabella al punto A ho appunto la chiave primaria composta da più record. Se voglio usare questa chiave (tutta o in parte) come foreign key di altre tabelle, come ci si comporta?
Va definito un costraint per ogni colonna o uno solo per a chiave contenenente le varie colonne?

Eventuali consigli sono graditi.
Grazie.

[piero.mac]

Comincio con il punto B

Arrivati a 255 poiche' il campo e' un solo byte succede che sommando 1 questo vada in overflow. Quindi:

  1111 1111 + 
          1 =
-------------
1 0000 0000

il contatore da un byte quindi torna a zero e l'overflow perso. Se non hai altro riferimento che non sia oltre al fatto di avere un numerello id allora ipotesi 1. ma pero' se ti serve sapere quale e' l'ultimo id inserito va da se che la via non e' percorribile, ne il punto 1 ne il punto 2.

Punto A. La chiave primaria su piu' campi puo' avere uno dei campi autoincrementanti. comincera' una nuova sequenza per ogni accoppiata campo-campo_autoincrement.

Occhio che last_insert_id() funziona solo se e' autoincrement cosi' come mysql_insert_id()

Per la foreign key mi pare, ma non certissimo, che la dichiarazione del REFERENCES preveda "nome della chiave" (lista dei campi)

[skidx]

Intanto grazie!

Originariamente inviato da piero.mac
il contatore da un byte quindi torna a zero e l'overflow perso. Se non hai altro riferimento che non sia oltre al fatto di avere un numerello id allora ipotesi 1. ma pero' se ti serve sapere quale e' l'ultimo id inserito va da se che la via non e' percorribile, ne il punto 1 ne il punto 2.

Per sapere ciò, al momento dell'inserimento userò anche un campo datetime, che tanto mi serviva comunque per tenere ordinati i record. In questo modo non dovrei avere problemi.
Con questi presupposti quale scelta è preferibile secondo te tra le due?

Punto A. La chiave primaria su piu' campi puo' avere uno dei campi autoincrementanti. comincera' una nuova sequenza per ogni accoppiata campo-campo_autoincrement.

Credo di non aver capito benissimo.
Supponiamo di avere il seguente record

id   | key1 |
1    | 1    |
1    | 2    |
1    | 3    |
1    | 4    |

Se la mia chiave primaria è id + key1, dove id è l'identificativo di un oggetto e key1 ne indica le sue versioni (il limite del punto B mi serve appunto per key1), tu dici di mettere key1 come autoincrement?
In ogni caso però ho il problema di generare il nuovo id per un altro oggetto, e si torna quindi al punto A.
Vale la mia soluzione usando MAX()?

[piero.mac]

Se utilizzi due chiavi una sara' messa da te, l'altra da autoincrement in modo automatico e progressivo

id   | key1 |
1    | 1    |
1    | 2    |
2    | 1    |
2    | 2    |
3    | 1    |
1    | 3    |
4    | 1    |
3    | 2    |

otterrai qualcosa del genere.

Non dovrebbe servire usare MAX() se si autoincrementa singolarmente.

Per l'ordinamento tramite data, ricorda che la data immessa con una query per generare/modificare record multipli sara' sempre la stessa cioe' identica, poiche' l'ora di sistema viene letta una volta sola per tutti.

[piero.mac]

Attento che parlo in linea di massima e non in modo assoluto per tutti gli engines e per tutte le versioni di Mysql. In 4.0.x e' cosi', poi ... bisogna provare.

Ci sono variazioni per adeguarsi pian piano a maxDB ed allo standard SQL99. Purtroppo (si fa per dire) bisogna riferirsi sempre al manuale per verificare le ultime novita'. Suppongo che si stia andando verso la fusione di maxDB/MySQL (IMHO).

[skidx]

Originariamente inviato da piero.mac
Se utilizzi due chiavi una sara' messa da te, l'altra da autoincrement in modo automatico e progressivo

id   | key1 |
1    | 1    |
1    | 2    |
2    | 1    |
2    | 2    |
3    | 1    |
1    | 3    |
4    | 1    |
3    | 2    |

otterrai qualcosa del genere.

Non dovrebbe servire usare MAX() se si autoincrementa singolarmente.

No, aspè, forse non ci siamo capiti.
Tra le due colonne, abbiamo detto che una delle due è autoincrement, l'altra no.
Quindi devo trovare un modo per generare, nel momento in cui mi serve, un valore univoco per l'altra colonna, quella che non è autoincrement.
Io pensavo di leggere con MAX() l'ultimo valore e di incrementare quello.

[piero.mac]

Originariamente inviato da skidx
Io pensavo di leggere con MAX() l'ultimo valore e di incrementare quello.

Ok. va bene allora.