Corso MariaDB / Identificare le righe con PRIMARY KEY, AUTO_INCREMENT e UNIQUE
PRIMARY KEY identifica in modo univoco ogni riga, mentre UNIQUE impedisce di ripetere altri valori o combinazioni di valori. AUTO_INCREMENT genera identificatori interi: non sostituisce la dichiarazione di una chiave e non garantisce una sequenza senza salti. Usare queste caratteristiche insieme in base all'identità dei dati e alle regole dell'applicazione.
Separare l'identificatore della riga dalla regola di unicità
Due clienti diversi possono avere lo stesso nome, quindi il nome non è un identificatore affidabile. L'esempio usa un numero cliente stabile generato automaticamente come chiave primaria e considera l'unicità dell'indirizzo email una regola separata. La guida alla struttura delle tabelle mostra dove dichiarare questi elementi. Affiancare le regole di presenza dei valori e i vincoli CHECK secondo i requisiti dei dati.
| Caratteristica | Ruolo | Condizione importante |
|---|---|---|
| PRIMARY KEY | Identificare una riga | Una definizione per tabella; le colonne non ammettono NULL e le combinazioni sono univoche |
| UNIQUE | Rifiutare valori o combinazioni di colonne ripetuti | Sono possibili più definizioni; una colonna UNIQUE nullable può contenere più NULL |
| AUTO_INCREMENT | Generare un intero quando l'identificatore viene omesso | Una colonna per tabella, indicizzata; possono esserci numeri mancanti |
Una chiave primaria non significa necessariamente una sola colonna. Una chiave primaria composta è un'unica definizione che comprende più colonne. AUTO_INCREMENT non deve essere la chiave primaria in ogni possibile progetto, ma una singola chiave primaria intera è un punto di partenza semplice per questo esercizio.
Eseguire l'esempio completo con clienti e chiavi
Collegarsi come course_user al server di studio con il database shopcourse disponibile. La guida al client e ai file SQL spiega collegamento e SOURCE. Eseguire i controlli nella connessione, non nella shell del sistema operativo:
SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;
Il database selezionato deve essere shopcourse. Salvare l'esempio normale completo come file UTF-8 .sql, quindi eseguirlo con SOURCE e il percorso locale reale. Tenere separate le istruzioni che devono fallire perché un errore può interrompere il file. Usare un'istanza di studio e verificare i nomi delle tabelle esistenti prima di crearle.
customers_key_lab è una tabella di esercizio separata per imparare a identificare le righe prima di modellare clienti e ordini. L'esempio permette un indirizzo email sconosciuto rendendo la colonna nullable, cioè capace di contenere NULL. La colonna della chiave primaria è dichiarata esplicitamente NOT NULL e viene omessa dagli INSERT dei clienti:
USE shopcourse;
CREATE TABLE customers_key_lab (
customer_id INT NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(254) NULL,
PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
('Alex Chen', 'alex@example.com'),
('Sam Patel', NULL),
('Jordan Lee', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
supplier_id INT NOT NULL,
supplier_code VARCHAR(30) NOT NULL,
label VARCHAR(100) NOT NULL,
PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
(1, 'N001', 'Notebook'),
(2, 'N001', 'Notebook from supplier two');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
item_id INT PRIMARY KEY AUTO_INCREMENT,
label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;
| customer_id | customer_name | |
|---|---|---|
| 1 | Alex Chen | alex@example.com |
| 2 | Sam Patel | NULL |
| 3 | Jordan Lee | NULL |
In una tabella nuova, con incremento e valore di partenza della sequenza configurati ai valori predefiniti 1, gli identificatori dei clienti sono 1, 2 e 3. UNIQUE(email) permette entrambi gli indirizzi NULL e non impone di compilare l'email. Se l'indirizzo è obbligatorio, aggiungere una regola NOT NULL appropriata ed esaminare le righe NULL già presenti prima di applicarla a una tabella esistente.
Il riferimento AUTO_INCREMENT e il riferimento dei vincoli PRIMARY KEY e UNIQUE descrivono le definizioni. Gli indirizzi example.com sono semplici valori di esempio nel database, non destinatari ai quali inviare messaggi.
Provare email e valori di chiave primaria duplicati
Dopo il file normale, eseguire separatamente queste due istruzioni intenzionalmente errate. La prima ripete un indirizzo email esistente; la seconda fornisce esplicitamente l'identificatore cliente 1:
INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');
INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');
Confrontare i nomi dei vincoli nei messaggi di errore. Dopo ogni fallimento, SELECT COUNT(*) FROM customers_key_lab deve ancora restituire tre. Non passare subito a INSERT IGNORE o REPLACE per nascondere il problema. REPLACE può sostituire una riga esistente in conflitto e comporta quindi effetti diversi dal semplice ignorare un duplicato.
La collazione della colonna email influisce sul confronto. Con utf8mb4_unicode_ci, cambiare maiuscole e minuscole può produrre comunque un duplicato: ALEX@example.com non è un valore distintamente accettabile rispetto ad alex@example.com secondo quella regola. Scegliere consapevolmente normalizzazione e confronto, senza presumere che grafie memorizzate diverse superino sempre UNIQUE. La spiegazione dei tipi stringa e delle collazioni fornisce il contesto.
Comprendere una chiave primaria formata da più colonne
Supporre che due fornitori usino entrambi il codice prodotto N001. Una chiave sul solo codice rifiuterebbe il prodotto valido del secondo fornitore. L'esempio completo definisce invece la coppia (supplier_id, supplier_code) come chiave primaria:
| supplier_id | supplier_code | Risultato |
|---|---|---|
| 1 | N001 | Codice del primo fornitore |
| 2 | N001 | Consentito: combinazione diversa |
| 1 | Ancora N001 | Rifiutato: stessa combinazione |
INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');
La chiave composta rende univoca la combinazione, non ogni componente separatamente. Un fornitore può avere più codici e fornitori diversi possono condividere un codice. Le query e i riferimenti tramite chiave esterna possono richiedere la coppia completa. Se una chiave aziendale può cambiare, valutare un identificatore stabile generato automaticamente con una combinazione UNIQUE separata, invece di allargare la chiave primaria soltanto per comodità.
Prevedere numeri mancanti negli identificatori automatici
La sezione auto_gap_lab inserisce una riga in una transazione, la annulla con ROLLBACK e poi salva una riga diversa. In questo esempio InnoDB nuovo, con l'incremento predefinito, la riga memorizzata ha identificatore 2 ed etichetta Saved row, cioè riga salvata. L'identificatore iniziale riservato dall'inserimento annullato non viene restituito alla sequenza.
Anche eliminazioni, inserimenti falliti e operazioni simultanee possono lasciare salti. AUTO_INCREMENT non è quindi il numero di righe e non garantisce una numerazione delle fatture senza interruzioni. Contare le righe con COUNT(*); MAX(id) indica soltanto l'identificatore più alto visibile.
Per recuperare il valore generato, chiamare LAST_INSERT_ID() nella stessa connessione subito dopo l'INSERT pertinente riuscito. Per un INSERT di più righe restituisce il primo identificatore automatico generato dall'istruzione, non l'elenco completo. Non mescolarlo con il lavoro di un'altra connessione né interpretare il valore dopo un INSERT fallito come prova di successo. Calcolare MAX(id)+1 manualmente può far scegliere lo stesso numero a client concorrenti e non sostituisce una generazione sicura.
Esaminare le chiavi e reimpostare le tabelle separate
SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;
Verificare che PRIMARY e uq_customers_key_email siano chiavi separate e che la chiave primaria composta includa entrambe le colonne previste. Il campo Extra di DESCRIBE e SHOW INDEX rispondono a domande diverse: generare valori e imporre unicità sono caratteristiche distinte.
Prima di ripetere, usare SELECT DATABASE() e SHOW CREATE TABLE con ogni nome reale per confermare che le destinazioni siano customers_key_lab, product_codes_key_lab e auto_gap_lab create qui. Salvare i dati da conservare. Eliminare solo dopo aver verificato le destinazioni:
DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_lab;
Questa istruzione rimuove le tre tabelle nominate e le loro righe. DROP TABLE non viene annullato da un normale ROLLBACK. Si può quindi eseguire nuovamente l'esempio normale completo. La tabella products della prima esercitazione sui prodotti resta fuori da questa reimpostazione.




