Un vincolo CHECK
consente di specificare che i valori di una o più colonne devono soddisfare un'espressione booleana. In questo articolo descriveremo come gestire questo
tipo di vincolo nel tuo database.
Aggiungi un vincolo di controllo a una nuova tabella
Nel seguente snippet CREATE TABLE
viene creata una tabella per memorizzare le informazioni
relative ai 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
. In questo esempio, il vincolo è stato espressamente denominato start_before_end
, al fine di semplificarne l'identificazione nei messaggi di errore e in tutti i casi in cui è necessario farvi riferimento. Se non viene
assegnato alcun nome, Spanner ne fornisce uno, con il nome generato che inizia
con il prefisso CK_
. I nomi dei vincoli hanno un ambito nello schema, insieme ai nomi delle tabelle e degli indici, e devono essere univoci all'interno dello schema. La
definizione del vincolo di controllo è composta 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 ed EndTime e questo controllo garantisce che l'ora di inizio di un concerto sia sempre inferiore all'ora di fine.
Il valore dell'espressione del vincolo di controllo viene valutato quando viene inserita una nuova riga o quando vengono aggiornati i valori 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.
Le seguenti limitazioni si applicano a un termine del 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 è impostata l'opzione
allow_commit_timestamp
.L'espressione non può contenere sottoquery.
L'espressione non può contenere funzioni non deterministiche, come
CURRENT_DATE()
eCURRENT_TIMESTAMP()
.
Aggiungi 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 al vincolo un nome: concert_id_gt_0. L'aggiunta di un vincolo CHECK
a una tabella esistente avvia l'applicazione immediata del vincolo per i nuovi dati e avvia un'operazione a lunga esecuzione per convalidare la conformità dei dati esistenti al nuovo vincolo. Poiché questa convalida viene eseguita come operazione a lunga esecuzione, le transazioni in corso nella tabella non sono interessate. Per maggiori informazioni, consulta la sezione Prestazioni dell'aggiornamento dello schema. In caso di violazioni dei dati esistenti, viene eseguito il rollback del vincolo.
Rimuovi un vincolo di controllo
La seguente istruzione DDL elimina un vincolo CHECK
dalla tabella Concerts
.
ALTER TABLE Concerts
DROP CONSTRAINT concert_id_gt_0;
Modifica un'espressione del vincolo di controllo
Non è consentita la modifica dell'espressione di un vincolo CHECK
. Devi invece eliminare il vincolo esistente e crearne uno nuovo con la nuova espressione.
Visualizza le proprietà di un vincolo di controllo
INFORMATION_SCHEMA di Spanner contiene informazioni sui vincoli di controllo sul tuo database. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere eseguendo una 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 verifica nel mio database?
Se hai aggiunto un vincolo di controllo a una tabella esistente, potrebbe essere utile 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 query seguente, significa che Spanner è ancora in fase di convalida dei dati esistenti in base a quel vincolo.
SELECT cc.CONSTRAINT_NAME, cc.SPANNER_STATE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS as cc;
Passaggi successivi
- Scopri di più sullo SCHEMA DI INFORMAZIONI di Spanner.