Creare e gestire i vincoli check

Un vincolo CHECK consente di specificare che i valori di una o più colonne devono soddisfare un'espressione booleana. In questa pagina viene descritto come aggiungere e gestire questo tipo di vincolo nei database di dialetti GoogleSQL e PostgreSQL.

Aggiungere un vincolo di controllo a una nuova tabella

Nel seguente snippet CREATE TABLE, creiamo una tabella per archiviare informazioni sui concerti. Per richiedere che l'ora di fine di un concerto sia successiva all'ora di inizio, includiamo un vincolo di controllo.

GoogleSQL 

CREATE TABLE Concerts (
  ConcertId INT64,
  StartTime Timestamp,
  EndTime Timestamp,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
) PRIMARY KEY (ConcertId);

PostgreSQL 

CREATE TABLE Concerts (
  ConcertId BIGINT,
  StartTime TIMESTAMPTZ,
  EndTime TIMESTAMPTZ,
  CONSTRAINT start_before_end CHECK(StartTime < EndTime),
  PRIMARY KEY (ConcertId)
);

La definizione del vincolo inizia con la parola chiave CONSTRAINT. Abbiamo denominato esplicitamente il vincolo start_before_end in questo esempio per aiutarti a trovarlo nei messaggi di errore e ogni volta che dobbiamo farvi riferimento. Se non viene fornito alcun nome, Spanner ne fornisce uno, con il nome generato che inizia con il prefisso CK_. I nomi dei vincoli sono inclusi nello schema, insieme ai nomi di tabelle e indici, e devono essere univoci all'interno dello schema. La definizione del vincolo di controllo è costituita dalla parola chiave CHECK seguita da un'espressione tra parentesi. L'espressione può fare riferimento solo alle colonne di questa tabella. In questo esempio, fa riferimento a StartTime e EndTime e il vincolo di controllo garantisce che l'ora di inizio di un concerto sia sempre precedente all'ora di fine.

Il valore dell'espressione del vincolo di controllo viene valutato quando viene inserita una nuova riga o quando vengono aggiornati StartTime o EndTime di una riga esistente. Se l'espressione restituisce TRUE o NULL, la modifica dei dati è consentita dal vincolo di controllo. Se l'espressione restituisce FALSE, la modifica dei dati non è consentita.

  • Si applicano le seguenti limitazioni a un termine di vincolo di controllo expression.

    • L'espressione può fare riferimento solo alle colonne della stessa tabella.

    • L'espressione deve fare riferimento ad almeno una colonna non generata, direttamente o tramite una colonna generata che fa riferimento a una colonna non generata.

    • L'espressione non può fare riferimento a colonne in cui è stata impostata l'opzione allow_commit_timestamp.

    • L'espressione non può contenere query secondarie.

    • L'espressione non può contenere funzioni non deterministiche, ad esempio CURRENT_DATE() e CURRENT_TIMESTAMP().

Aggiungere un vincolo di controllo a una tabella esistente

Utilizzando la seguente istruzione ALTER TABLE, aggiungiamo un vincolo per assicurarci che tutti gli ID concerto siano maggiori di zero.

ALTER TABLE Concerts
ADD CONSTRAINT concert_id_gt_0 CHECK (ConcertId > 0);

Ancora una volta, abbiamo assegnato un nome al vincolo, concert_id_gt_0. L'aggiunta di un vincolo CHECK a una tabella esistente avvia l'applicazione del vincolo immediatamente per i nuovi dati e avvia un'operazione a lunga esecuzione per verificare che i dati esistenti siano conformi al nuovo vincolo. Poiché questa convalida viene eseguita come operazione a lunga esecuzione, le transazioni in corso nella tabella non sono interessate. Per ulteriori informazioni, vedi Rendimento dell'aggiornamento dello schema. Se vengono rilevate violazioni nei dati esistenti, il vincolo viene ripristinato.

Rimuovere un vincolo di controllo

La seguente istruzione DDL elimina un vincolo CHECK dalla tabella Concerts.

ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;

Modificare un'espressione di vincolo di controllo

Non è consentita la modifica dell'espressione di un vincolo CHECK. Devi eliminare il vincolo esistente e crearne uno nuovo con la nuova espressione.

Visualizzare le proprietà di un vincolo di controllo

INFORMATION_SCHEMA di Spanner contiene informazioni sui vincoli di controllo del tuo database. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere eseguendo query sullo schema delle informazioni.

Quali vincoli di controllo sono definiti nel mio database?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = 'CHECK';

Qual è lo stato attuale dei vincoli di controllo nel mio database?

Se hai aggiunto un vincolo di controllo a una tabella esistente, ti consigliamo di visualizzarne lo stato attuale per determinare, ad esempio, se tutti i dati esistenti sono stati convalidati in base al vincolo. Se SPANNER_STATE restituisce VALIDATING_DATA nella seguente query, significa che Spanner è ancora in fase di convalida dei dati esistenti in base a questo vincolo.

SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;

Passaggi successivi