MySQL e il vincolo CHECK per le colonne

CHECK è un vincolo di integrità (constraint) del linguaggio SQL che può essere utilizzato per specificare e limitare l'intervallo di valori memorizzabili all'interno di un campo. Questo significa che esso permette di stabilire cosa sia possibile allocare in una determinata colonna non in base al tipo di dato associato ma in considerazione del valore proposto in fase di INSERT o aggiornamento. Il suo funzionamento ricorda quindi quello della clausola WHERE che include o esclude i valori che presentano caratteristiche specifiche dal risultato di una query.

In MySQL tale clausola può essere utilizzata nella creazione delle tabelle, quindi con CREATE TABLE, e nelle istruzioni ALTER TABLE. La sintassi di CHECK permette anche di imporre un vincolo su più colonne alla volta.

Creare una tabella con il vincolo CHECK

Per proporre un primo esempio riguardante l'uso del vincolo CHECK è possibile creare una tabella basata sullo storage engine InnoDB chiamata Anagrafiche in cui sono presenti 4 colonne:

• ID_anagrafica: un campo autoincrementale NOT NULL di tipo intero che funge da chiave primaria;
• Nome: un primo campo NOT NULL di tipo VARCHAR della lunghezza massima di 100 caratteri;
• Cognome: un secondo campo NOT NULL di tipo VARCHAR della lunghezza massima di 100 caratteri;
• Anni: un campo destinato ad ospitare unicamente numeri interi (tipo di dato INT).

L'istruzione da utilizzare per ottenere il risultato in assenza di vincoli è normalmente la seguente:

CREATE TABLE Anagrafiche (
    ID_anagrafica int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    Cognome VARCHAR(100) NOT NULL,
    Anni INT
) ENGINE=InnoDB;

Ora, immaginiamo che la tabella sia destinata ad ospitare soltanto alcune informazioni personali relative a utenti pensionati. Questo significa che l'età di questi ultimi non potrà essere al di sotto di una certa soglia. Nell'istruzione seguente questa è stata stabilita in 60 anni che è chiaramente un valore scelto arbitrariamente ma rappresenta un limite indicativo per il vincolo che si desidera definire.

CREATE TABLE Anagrafiche (
    ID_anagrafica int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    Cognome VARCHAR(100) NOT NULL,
    Pensione ENUM('pubblico', 'privato'),
    Anni INT,
    CHECK (Anni >= 60)
) ENGINE=InnoDB;

Si noti anche come in questo caso sia stato aggiunto un nuovo campo con tipo di dato ENUM, Pensione, che accetta soltanto due valori predefiniti: "pubblico" e "privato". La parte più interessante riguarda però l'uso della clausola:

CHECK (Anni >= 60)

CHECK prevede infatti che il vincolo debba essere specificato tra parentesi tonde ed espresso tramite operatori di confronto il cui risultato può essere soltanto TRUE, FALSE o NULL.

Vincolo CHECK su più colonne

Come anticipato, il vincolo della clausola CHECK può essere imposto su più colonne, incrementando il numero di condizioni che devono essere rispettate per l'inserimento dei valori nei campi coinvolti. Ipotizziamo ad esempio di voler aggiungere alla nostra tabella Anagrafiche un nuovo vincolo basato sull'appartenenza regionale:

CREATE TABLE Anagrafiche (
    ID_anagrafica int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nome VARCHAR(100) NOT NULL,
    Cognome VARCHAR(100) NOT NULL,
    Pensione ENUM('pubblico', 'privato'),
    Anni INT,
    Regione VARCHAR(30),
    CHECK (Anni >= 60 AND Regione = 'Sardegna')
) ENGINE=InnoDB;

Nella nuova istruzione abbiamo aggiunto innanzitutto la nuova colonna Regione con tipo di dato VARCHAR e una lunghezza massima di 30 caratteri. Il vincolo su più campi viene espresso invece in questo modo:

CHECK (Anni >= 60 AND Regione = 'Sardegna')

Le due condizioni vengono concatenate tramite l'operatore logico AND con il quale abbiamo indicato che entrambi i vincoli devono essere rispettati. È possibile inoltre dare un nome al vincolo correggendo il codice in questo modo e introducendo l'istruzione CONSTRAINT:

..
CONSTRAINT CHK_anagrafiche CHECK (Anni >= 60 AND Regione = 'Sardegna')
..

Nel caso in cui il nome del vincolo non dovesse essere esplicitato, il DBMS provvederà a generarne uno automaticamente con il formato nometabella_chk_numero. Nel nostro caso avremmo ad esempio Anagrafiche_chk_1 per il primo vincolo, Anagrafiche_chk_2 per un eventuale secondo vincolo e così via.

CHECK: alterazione delle tabelle e rimozione del vincolo

La clausola CHECK può essere introdotta sia al momento della generazione di una nuova tabella che in un momento successivo, su una tabella già creata, tramite ALTER TABLE. Nel caso della tabella Anagrafiche, se il vincolo basato sulla colonna Anni non fosse stato già definito lo si potrebbe imporre in questo modo:

ALTER TABLE Anagrafiche
ADD CHECK (Anni >= 60);

La stessa operazione può essere effettuata anche quando si vogliono introdurre dei vincoli multipli su più campi di una tabella già esistente:

ALTER TABLE Anagrafiche
CONSTRAINT CHK_anagrafiche CHECK (Anni >= 60 AND Regione = 'Sardegna');

L'istruzione ALTER TABLE può essere utilizzata infine per la rimozione di un vincolo sfruttando la seguente sintassi dove il nome del constraint da eliminare viene preceduto dal comando DROP:

ALTER TABLE Anagrafiche
DROP CONSTRAINT CHK_anagrafiche;

Il codice mostrato è molto semplice, vale però la pena di notare come possa essere utile associare un nome personalizzato ad un vincolo. In questo modo, in presenza di più constraint sarà più facile ricordare il nome di quella che si desidera cancellare.

Limiti di CHECK

Ampliando il discorso riguardante CHECK oltre il solo MySQL è importante tenere presente che parliamo di un vincolo di integrità generico. A differenza di altri vincoli di integrità, come per esempio PRIMARY KEY e FOREIGN KEY, CHECK non è un costrutto proprio (e quindi nativo) del modello relazionale. Questo significa che prima di utilizzarlo è buona norma verificare che l'RDBMS di riferimento lo supporti, non ignorandolo, e che il suo impiego non influisca negativamente sul workload di quest'ultimo. Diversamente non sono da escludere dei cali performance durante l'esecuzione delle istruzioni.

Nel caso specifico di MySQL, prima del rilascio della versione 8.0.16 il supporto per i constraint basati su CHECK era limitato. Per questo motivo le clausole CHECK introdotte nelle definizioni delle tabelle venivano ignorate dal Database Engine. L'unico modo per implementare dei controlli personalizzati sui dati in ingresso era quindi quello di ricorre ai trigger o ad altre soluzioni simili. Dopo la release 8.0.16 di MySQL il supporto a CHECK è stato incluso a livello di core e tale vincolo è utilizzabile indipendentemente dallo storage engine scelto per la creazione di una tabella.

Prima di definire un constraint basato su CHECK con MySQL è quindi utile verificare che la propria istanza del DBMS sia in grado di interpretarlo.

Conclusioni

CHECK è un vincolo di integrità che possiamo sfruttare quando creiamo o modifichiamo la tabella di un database MySQL. Permette infatti di limitare l'intervallo di valori che può essere memorizzato in una colonna.