MySQL: chiave primaria (primary key) a due colonne con auto-increment

Chiunque lavori abitualmente con MySQL (e con i DBMS in generale) conosce bene l’attributo     e come utilizzarlo per fare in modo che una colonna di una tabella – spesso la chiave primaria – contenga una identità univoca per ciascun record. Un ottimo esempio dell’utilizzo di questo attributo si può trovare nella documentazione ufficiale di MySQL:

che restituisce i seguenti risultati:

Detto questo, potremmo chiederci cosa succede nei casi in cui la nostra chiave primaria è articolata su due colonne e l’attributo   in questione è presente soltanto in una di queste.

Il counter riparte da 1 ad ogni cambio di valore dell’altra colonna?

Oppure la numerazione progressiva viene mantenuta a prescindere?

La risposta dipende sia dal DB Engine utilizzato che, soprattutto, da come vengono creati gli indici della tabella all’interno del comando   . La documentazione ufficiale dice che:

For MyISAM and BDB tables you can specify  on a secondary column in a multiple-column index. In this case, the generated value for the   column is calculated as  . This is useful when you want to put data into ordered groups.

Questo significa che, se vogliamo che la nostra tabella si comporti come nella prima ipotesi, dobbiamo crearla inserendo la colonna    prima della colonna    quando impostiamo la    come nell’esempio seguente:

Inutile dire che, invertendo l’ordine delle colonne nella definizione della  , otterremo il risultato mostrato nella seconda ipotesi.

Alcune considerazioni da fare:

  • Ricordate sempre che – come spiegato abbondantemente dalla documentazione ufficiale di MySQL – i valori impostati dall’attributo    vengono sempre riutilizzati nei casi in cui viene eliminato il valore più alto in qualsiasi gruppo di chiavi primarie. Questo significa che, se imposterete la   per ottenere quanto descritto nella prima ipotesi, questo accadrà per ciascuno dei gruppi    che verranno a crearsi.
  • Se la colonna    è presente in altri indici relativi alla tabella, ed è la prima colonna ad essere menzionata in almeno uno di questi, i valori saranno generati prendendo in considerazione quell’indice a prescindere da quanto definito a livello di chiave primaria. In altre parole, se la tabella    descritta in precedenza contenesse un indice di tipo   , MySQL ignorerebbe l’indice corrispondente alla chiave primaria e genererebbe valori univoci per la colonna  , ottenendo così il risultato descritto nella seconda ipotesi.
  • IMPORTANTE: Se state utilizzando questa tecnica in un cluster di DB in replication e la vostra versione di MySQL è inferiore alla 5.1.65 rischierete di incorrere in alcuni problemi descritti dalla documentazione ufficialeAn INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based logging or replication. Beginning with MySQL 5.1.64, such statements are marked as unsafe. (Bug #11754117, Bug #45670). Fortunatamente, questi bug sono stati risolti in conseguenza del rilascio di MySQL 5.1.65.

Purtroppo, nel caso in cui la vostra tabella utilizzi InnoDB, non c’è modo di ottenere lo stesso risultato mediante strumenti nativi dell’engine: questo perché, come spiegato nella documentazione ufficialeany InnoDB table with an  column requires at least one key where the auto-increment column is the only or leftmost column. Se avete l’esigenza di ottenere un risultato analogo a quello della prima ipotesi per una tabella InnoDB, potete sfruttare le potenzialità del clustered composite index di InnoDB, come descritto in questo intervento sul sito StackOverflow, e vedere se può adattarsi alla vostra situazione.

Questo è quanto: felice sviluppo!

 

RELATED POSTS

About Ryan

IT Project Manager, Web Interface Architect e Lead Developer di numerosi siti e servizi web ad alto traffico in Italia e in Europa. Dal 2010 si occupa anche della progettazione di App e giochi per dispositivi Android, iOS e Mobile Phone per conto di numerose società italiane.

View all posts by Ryan