Allora per ordine
1) se vuoi un campo autoincrementante, la logica è quella di usare un campo autoincrementante (che, però, può essere unico per ogni tabella)
2) se usi mariadb >=10.3 puoi usare le sequenze (che è quello che vuoi tentare di fare)
3) in un "mondo normale" non si usano campi autoincrementanti per fare quello che vuoi, ossia una chiave primaria.
Puoi generarla benissimo da applicazione, anche pseudocausale, e usare quella (* nota: avrai una riduzione delle performances nel caso di chiavi lunghe, di scattering sulle foglie dell'indice se pseudocasuale. direi che se hai un'applicazione di dimensioni risibili e usi la chiave per join 1-1 la cosa non è che ti turbi più di tanto, direi per nulla)
4) se sei "incaponito" puoi (in via alternativa, ma sono approcci inadatti per applicazioni "serie")
a) inserire una riga con id vuoto, e poi fare un update che setta al massimo+1 quella con riga vuota (non funziona affidabilmente in concorrenza)
b) leggere da applicazione il MAX del campo, e quando fai l'insert scrivere il massimo +1 (non funziona affidabilmente in concorrenza)
c) fare un trigger BEFORE INSERT
del tipo
DELIMITER //
CREATE TRIGGER candidato_after_insert
AFTER INSERT ON candidato FOR EACH ROW
BEGIN
... aggiorna il contatore
END; //
DELIMITER ;
(non funziona affidabilmente in concorrenza in tutti i casi, ma è molto meglio di a e b)
d) fare B ma con un
LOCK TABLES candidato WRITE;
... prendi il MAX del campo
... fai il resto
UNLOCK TABLES;
...insomma... come vedi ti stai facendo parecchi problemi rispetto a (3)