Corso MariaDB / Definire gli input con NULL, DEFAULT, NOT NULL e CHECK

NULL indica informazioni mancanti o sconosciute ed è diverso da zero e da una stringa vuota. DEFAULT fornisce un valore quando l'input viene omesso, NOT NULL rifiuta NULL e CHECK verifica una condizione. Combinare queste regole perché ciascuna risolve un problema di validazione diverso.

Separare NULL, stringa vuota, zero e colonna omessa

Stato dell'input Significato Esempio
NULL Mancante, sconosciuto o non ancora determinato Una nota non è stata raccolta
Stringa vuota '' Una stringa nota di lunghezza zero Una nota è stata inviata senza testo
0 Il valore numerico zero Nessuna unità disponibile
Colonna omessa Non compare nell'elenco delle colonne INSERT Il valore predefinito di stock fornisce zero

Un'applicazione può decidere di trattare allo stesso modo una nota non raccolta e una nota vuota, ma è una politica dei dati. Sostituire automaticamente tutti i NULL con stringhe vuote impedisce di distinguerli. L'esercizio usa la gestione ordinaria delle stringhe senza modi di compatibilità come EMPTY_STRING_IS_NULL.

La maggior parte dei confronti ordinari con NULL produce un risultato sconosciuto, rappresentato da NULL, anziché vero o falso. Usare IS NULL, non = NULL, per verificarne la presenza. Il riferimento dei valori NULL spiega operatori ed eccezioni.

Assegnare a ogni vincolo la propria responsabilità

Definizione Che cosa fa Che cosa non garantisce
DEFAULT 0 Fornisce zero quando la colonna è omessa o viene richiesto DEFAULT Non sostituisce in generale ogni NULL fornito esplicitamente
NOT NULL Rifiuta il marcatore di valore mancante Non vieta stringhe vuote, zero o tutti i valori negativi
CHECK (stock >= 0) Rifiuta una condizione di scorta non negativa che risulta falsa Da solo non rifiuta NULL in una colonna nullable
NOT NULL e CHECK Richiede un valore e ne controlla la condizione Non sostituisce i messaggi dell'applicazione o la verifica dei dati già presenti

Non usare un valore predefinito per inventare un'informazione sconosciuta. Se le scorte non sono ancora state contate, zero può significare erroneamente esaurito. L'esempio decide esplicitamente che un nuovo articolo inizi con zero unità; un'altra applicazione potrebbe richiedere NULL o uno stato di verifica separato.

I controlli dell'applicazione possono offrire messaggi utili. I vincoli del database proteggono invece il confine di memorizzazione anche quando scrive un altro programma, client o file SQL. Entrambi i livelli hanno uno scopo: validare soltanto il modulo visibile non vincola tutti i percorsi di scrittura.

Eseguire l'esempio completo con valori predefiniti e NULL

Collegarsi come course_user al server di studio con shopcourse disponibile. La guida al client e ai file SQL spiega collegamento e SOURCE. Eseguire i controlli dentro la connessione, non nella shell:

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 in un file UTF-8 .sql ed eseguirlo con SOURCE e il percorso locale reale. Tenere separati gli errori intenzionali perché possono interrompere il file. Usare un'istanza di studio e controllare le tabelle già presenti prima di crearle.

Il file normale inserisce soltanto righe valide. Salva modo SQL e impostazione CHECK della sessione, poi abilita esplicitamente modalità rigorosa e verifica CHECK. Su installazioni vecchie controllare la versione reale: accettare la sintassi non dimostra sempre che la condizione venga applicata. Il supporto CHECK è stato aggiunto in MariaDB 10.2.1, come riportato nelle note storiche di rilascio MariaDB 10.2.1.

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET @saved_check_constraints = @@SESSION.check_constraint_checks;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION check_constraint_checks = ON;
CREATE TABLE validation_lab (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(100) NOT NULL,
  note VARCHAR(100) NULL DEFAULT NULL,
  stock INT NOT NULL DEFAULT 0,
  CONSTRAINT ck_validation_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO validation_lab (item_id, item_name, note) VALUES
  (1, 'Notebook', NULL),
  (2, 'Mug', '');
SELECT item_id, note IS NULL AS note_is_null,
       note = '' AS note_is_empty, stock
FROM validation_lab ORDER BY item_id;
CREATE TABLE nullable_check_lab (
  item_id INT PRIMARY KEY,
  stock INT NULL,
  CONSTRAINT ck_nullable_stock CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO nullable_check_lab VALUES (1, NULL);
SELECT item_id, stock IS NULL AS stock_is_null FROM nullable_check_lab;
Riga note_is_null note_is_empty stock
1 1 NULL 0
2 0 1 0

La prima nota è NULL, quindi anche il confronto con la stringa vuota restituisce NULL. La seconda nota è una stringa vuota: IS NULL è falso e il confronto con la stringa vuota è vero. Entrambi gli INSERT omettono stock e ricevono quindi zero.

La tabella separata nullable_check_lab accetta scorte NULL. La sua espressione CHECK non risulta falsa per NULL. Se le scorte devono essere sempre note e non negative, occorrono NOT NULL e CHECK insieme; la sola condizione non negativa non basta.

Provare separatamente gli errori NOT NULL e CHECK

Dopo l'esempio valido, eseguire queste istruzioni una alla volta. La prima fornisce NULL per il nome obbligatorio; la seconda fornisce il nome ma viola la condizione sulle scorte:

INSERT INTO validation_lab (item_id, item_name) VALUES (3, NULL);
INSERT INTO validation_lab (item_id, item_name, stock) VALUES (3, 'Desk lamp', -1);

La prima deve segnalare che il valore non può essere NULL; la seconda deve identificare il vincolo sulle scorte. Dopo ogni errore, SELECT COUNT(*) FROM validation_lab deve restituire ancora due. L'identificatore 3 non viene salvato dal tentativo fallito e può quindi essere riutilizzato nel secondo test. Il testo dell'errore può cambiare secondo versione e lingua: individuare la colonna o il vincolo coinvolto.

Valore predefinito, forma dell'input e regole del tipo determinano se un NULL esplicito è ammesso, rifiutato o trattato in modo particolare. AUTO_INCREMENT e TIMESTAMP hanno comportamenti NULL speciali in alcuni casi. Non generalizzare questo esempio di normali colonne note e stock a qualsiasi tipo.

Controllare valori vuoti, nomi delle regole e applicazione dei vincoli

Una stringa NOT NULL può ancora essere vuota, come spiegato nella guida ai tipi stringa. Una regola come CHECK (CHAR_LENGTH(TRIM(item_name)) > 0) può rifiutare anche nomi vuoti o composti da soli spazi ordinari. Non presumere che TRIM predefinito rimuova ogni spazio Unicode o simbolo invisibile.

SHOW CREATE TABLE validation_lab;
SELECT @@SESSION.sql_mode, @@SESSION.check_constraint_checks;

Il nome ck_validation_stock facilita la ricerca della regola che fallisce. Usare nomi significativi che identifichino tabella, colonna o condizione e controllarne l'ambito di unicità quando si supportano versioni diverse. Il riferimento dei vincoli MariaDB descrive sintassi CHECK e controlli di applicazione.

Disabilitare check_constraint_checks può consentire dati contrari ai CHECK dichiarati. Correggere l'input o la regola voluta invece di disattivare la validazione solo per far riuscire una scrittura. Riattivare i controlli non significa che ogni riga non valida già memorizzata sia stata esaminata o riparata.

Ripristinare le impostazioni e ripetere solo l'esercizio

SET SESSION check_constraint_checks = @saved_check_constraints;
SET SESSION sql_mode = @saved_sql_mode;

Ripristinare le impostazioni nella stessa connessione che ha salvato le variabili. Dopo una disconnessione, queste non contengono più i valori precedenti: esaminare la nuova connessione invece di applicare variabili salvate vuote.

Prima di ripetere, usare SELECT DATABASE() e SHOW CREATE TABLE per validation_lab e nullable_check_lab per confermare che siano le tabelle di studio create qui. Salvare i dati da conservare. Solo dopo la verifica eliminare le destinazioni con:

DROP TABLE shopcourse.validation_lab, shopcourse.nullable_check_lab;

L'istruzione rimuove entrambe le tabelle nominate e le righe. DROP TABLE non viene annullato da un normale ROLLBACK. Si può poi ripetere l'esempio normale completo. La tabella products della prima esercitazione sui prodotti resta fuori da questa reimpostazione.