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

MySQL Master-Master Replication setup in 5 easy steps

Chiunque lavori abitualmente con MySQL (e con i DBMS in generale) conosce bene l’attributo   AUTO_INCREMENT  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 AUTO_INCREMENT  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  CREATE TABLE . La documentazione ufficiale dice che:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT  column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix . 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  grp  prima della colonna  id  quando impostiamo la  PRIMARY KEY  come nell’esempio seguente:

Inutile dire che, invertendo l’ordine delle colonne nella definizione della PRIMARY KEY , 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  AUTO_INCREMENT  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 PRIMARY KEY  per ottenere quanto descritto nella prima ipotesi, questo accadrà per ciascuno dei gruppi  AUTO_INCREMENT  che verranno a crearsi.
  • Se la colonna  AUTO_INCREMENT  è 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  animals  descritta in precedenza contenesse un indice di tipo  INDEX (id) , MySQL ignorerebbe l’indice corrispondente alla chiave primaria e genererebbe valori univoci per la colonna id , 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 AUTO_INCREMENT 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!

 

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. Microsoft MVP for Development Technologies dal 2018.

View all posts by Ryan

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.