Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Definizione dei dati > Vincoli
PrecedenteValori di DefaultColonne di SistemaSuccessivo

5.3. Vincoli

I tipi di dato sono un modo per limitare il genere dei dati che possono essere immagazzinati in una tabella. Il vincolo che forniscono, tuttavia è per molte applicazioni troppo grossolano. Per esempio, una colonna contenente il prezzo di un prodotto dovrebbe, probabilmente, accettare solo valori positivi. Ma non c'è nessun tipo di dato standard che accetti esclusivamente numeri positivi. Un altro problema è rappresentato dal voler vincolare il dato di una colonna rispetto ai dati di altre colonne o persino righe. Ad esempio, in una tabella contenente informazioni su un prodotto, ci dovrebbe essere soltanto una riga per ogni numero di prodotto.

Per questo, SQL permette di definire vincoli sulle colonne e sulle tabelle. I vincoli permettono di controllare a proprio piacimento i dati contenuti nelle tabella. Se un utente tenta di immagazzinare dati in una colonna violando un vincolo, viene generato un errore. Questo si applica anche se il valore proviene dalla definizione del valore di default.

5.3.1. Vincolo Check

Il vincolo check è del tipo più generico. Permette di specificare che il valore di una certa colonna debba soddisfare un'espressione booleana (valore-verità). Per esempio, per richiedere prezzi positivi per i prodotti, usare:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

La definizione di vincolo viene dopo il tipo di dato, esattamente come le definizioni dei valori di default. I valori di default e i vincoli possono essere elencati in qualsiasi ordine. Un vincolo check consiste della parola chiave CHECK seguita da un'espressione in parentesi. L'espressione del vincolo check dovrebbe coinvolgere la colonna vincolata, altrimenti il vincolo non avrebbe molto senso.

Si può anche dare un nome al vincolo. Questo chiarifica i messaggi di errore e permette di individuare il vincolo quando questo debba essere modificato. La sintassi è:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

Così, per specificare un vincolo con nome, usare la parola chiave CONSTRAINT seguita da un identificatore e dalla definizione del vincolo (qualora non venga specificato un nome in modo esplicito, il sistema ne sceglierà uno).

Un vincolo check può anche riguardare diverse colonne. Si supponga di avere un prezzo regolare e un prezzo scontato, e di volersi assicurare che il prezzo scontato sia minore del prezzo regolare:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

I primi due vincoli dovrebbero essere familiari. Il terzo usa una nuova sintassi. Esso non è vincolato a una particolare colonna ed appare invece come un oggetto separato all'interno della lista delle colonne separate da virgola. Le definizioni delle colonne e dei vincoli possono essere elencate in ordine sparso.

Il terzo è comunemente detto vincolo di tabella perchè non è legato a una particolare definizione di colonna. I vincoli di colonna possono anche essere scritti come vincoli di tabella, mentre il contrario non è possibile, dato che un vincolo di colonna si riferisce solo alla colonna per cui è definito (PostgreSQL™ non impone questa regola, ma dovrebbe essere seguita qualora si richieda che le definizioni delle tabelle funzionino con altri sistemi di database). L'esempio precedente avrebbe potuto essere scritto in uno dei seguenti modi:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

oppure:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

È una questione di gusto.

I nomi possono essere assegnati ai vincoli di tabella nello stesso modo in cui vengono assegnati ai vincoli di colonna:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

Un vincolo check è soddisfatto se l'espressione restituisce un valore true o null. Dato che la maggior parte delle espressioni restituirà null se uno degli operandi è null, esse non impediranno valori null nelle colonne vincolate. Per assicurare che una colonna non contenga valori null, può essere usato il vincolo not-null descritto nella prossima sezione.

5.3.2. Vincolo Not-Null

Un vincolo not-null semplicemente specifica che una colonna non può assumere in nessun caso il valore null. Un esempio di sintassi:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

Un vincolo not-null è sempre scritto come un vincolo di colonna. Un vincolo not-null funziona come un vincolo check della forma CHECK (column_name IS NOT NULL), ma in PostgreSQL™ creare esplicitamente un vincolo not-null è più efficiente. Il rovescio della medaglia è che non è possibile dare un nome in modo esplicito ai vincoli not-null creati in questo modo.

Ovviamente, una colonna può avere più di un vincolo. Semplicemente scrivere i vincoli uno dopo l'altro:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

L'ordine non fa testo e non determina necessariamente in quale ordine i vincoli vengano controllati.

Il vincolo NOT NULL ha un suo inverso: il vincolo NULL. Questo non significa che la colonna debba essere nulla (cosa alquanto inutile), quanto che il valore di default della colonna sia null. Il vincolo NULL non è presente nello standard SQL e non dovrebbe essere usato in applicazioni portabili (è stato aggiunto a PostgreSQL™ per essere compatibile con alcuni altri sistemi di database). Alcuni utenti lo preferiscono in quanto rende facile cambiare il vincolo in uno script. Ad esempio, potrebbe essere pratico iniziare con:

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

e poi inserire la parola chiave NOT dove desiderato.

[Suggerimento]

Suggerimento

In molti progetti di database la maggioranza delle colonne dovrebbe essere definita come not null.

5.3.3. Vincolo Unique

I vincoli unique assicurano che i dati contenuti in una colonna o un gruppo di colonne siano unici rispetto a tutte le righe nella tabella. La sintassi è:

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

quando scritto come vincolo di colonna, e:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

quando scritto come vincolo di tabella.

Se un vincolo unique si riferisce a un gruppo di colonne, le colonne sono elencate separate da virgole_

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

Questo specifica che la combinazione di valori nelle colonne indicate è unica in tutta la tabella, sebbene ogni singola colonna non necessiti di essere (e di solito non lo è) unica.

È possibile assegnare un nome per un vincolo unique, nel solito modo:

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

Aggiungere un vincolo unique creerà automaticamente un indice btree nella colonna, o gruppo di colonne, usate nel vincolo.

In generale, un vincolo unique è violato quando c'è più di una riga nella tabella dove i valori di tutte le colonne incluse nel vincolo sono uguali. Comunque, due valori null non sono considerati uguali in questa comparazione. Questo significa che anche in presenza di un vincolo unique è possibile immagazzinare righe duplicate che contengono un valore null in almeno una delle colonne vincolate. Questo comportamento è conforme allo standard SQL, ma abbiamo sentito che altri database SQL potrebbero non seguire questa regola. Così, stai attento quando sviluppi applicazioni che debbano essere portabili.

5.3.4. Chiavi Primarie

Tecnicamente, un vincolo chiave primaria è semplicemente una combinazione del vincolo unique e del vincolo not-null. Così, le due tabelle seguenti accettano gli stessi dati:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Le chiavi primarie possono anche vincolare più di una colonna; La sintassi è simile ai vincoli unique:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Una chiave primaria indica che una colonna o gruppo di colonne può essere usata come identificatore unico per le righe nella tabella. Questa è una diretta conseguenza della definizione di chiave primaria (notare che un vincolo unique non fornisce, da solo, un identificatore unico, perchè non esclude i valori null). Questo è utile sia per scopi di socumentazione, sia per applicazioni client. Per esempio, un'applicazione GUI che permette la modifica dei valori di una riga probabilmente ha bisogno di sapere la chiave primaria di una tabella per essere capace di identificare le righe unicamente.

Aggiungere una chiave primaria creerà automaticamente un indice btree unico sulla colonna o gruppo di colonne usate nella chiave primaria.

Una tabella può avere al più una chiave primaria. (Ci possono essere qualsiasi numero di vincoli unique e not-null, che sono funzionalmente la stessa cosa, ma solo uno può essere identificato come chiave primaria.) La teoria dei database relazionali afferma che ogni tabella deve avere una chiave primaria. Questa regola non è forzata da PostgreSQL™, ma è generalmente preferibile seguirla.

5.3.5. Chiavi Esterne

Un vincolo chiave esterna specifica che i valori in una colonna (o un gruppo di colonne) deve essere uguale ai valori presenti in qualche riga di un'altra tabella. Questo mantiene l'integrità referenziale tra due tabelle correlate.

Mettiamo di avere la tabella products, che abbiamo usato già diverse volte:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Assumiamo inoltre di avere una tabella che tiene gli ordini di questi prodotti. Vogliamo assicurare che la tabella degli ordini contengano solo ordini di prodotti che esistono realmente. Così definiamo un vincolo chiave esterna nella tabella ordini che fa riferimento alla tabella prodotti:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

Ora è impossibile creare ordini con valori di product_no che non appaiano nella tabella prodotti.

Diciamo che in questa situazione la tabella ordini è la tabella referente e la tabella prodotti è la tabella referenziata. Similarmente, ci sono colonne referenti e referenziate.

È possibile anche abbreviare il comando sopra:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

perchè in assenza di un elenco di colonne, la chiave primaria della tabella referenziata è usata come colonna/e referenziate.

Una chiave esterna puà anche vincolare e referenziare un gruppo di colonne. Come al solito, deve essere scritta nella forma dei vincoli di tabella. Ecco un esempio di sintassi contratta:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

Certamente, il numero e il tipo delle colonne vincolate necessita di coincidere col numero e il tipo delle colonne referenziate.

Si può assegnare un nome per un vincolo chiave esterna, nel solito modo.

Una tabella puà contenere più di un vincolo chiave esterna. Questo è usato per implementare relazioni molti-a-molti tra le tabelle. Mettiamo di avere tabelle riguardanti prodotti e ordini, ma ora si vuole permettere a un ordine di contenere molti prodotti (la struttura precedente non lo permetteva). Usare questa struttura di tabella:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Notare che la chiave primaria si sovrappone con le chiavi esterne nell'ultima tabella.

Sappiamo che le chiavi esterne non permettono la creazione di ordini che non siano correlati a qualche prodotto. Ma cosa succede se un prodotto viene rimosso dopo che un ordine che lo referenzia viene creato? SQL ti permette di gestire questa situazione. Intuitivamente, abbiamo diverse opzioni:

  • Non permettere la cancellazione di un prodotto referenziato

  • Cancellare anche gli ordini

  • Qualcos'altro?

Per spiegarlo, implementiamo il seguente comportamento sulla relazione molti-a-molti dell'esempio superiore: quando qualcuno vuole rimuovere un prodotto che è referenziato da un ordine (attraverso order_items), noi non glielo permettiamo. Se qualcuno rimuove un ordine, anche gli articoli dell'ordine verranno rimossi:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Le cancellazioni ristrette e in cascata sono le due opzioni più comuni. RESTRICT previene la cancellazione di una riga referenziata. NO ACTION significa che se esiste una riga referenziante quando il vincolo viene controllato, viene generato un errore; questo è il comportamento di default se non specifichi niente. (La differenza essenziale tra queste due scelte è che NO ACTION permette che il controllo venga rinviato successivamente nella transazione, mentre RESTRICT no.) CASCADE specifica che quando una riga referenziata viene cancellata, anche la/e riga/e referenti dovrebbero essere cancellate. Ci sono altre due opzioni: SET NULL e SET DEFAULT. Queste causano che le colonne referenti siano settate a null o al valore di default, rispettivamente, quando la riga referenziata viene cancellata. Notare che questo non ti esclude dall'avere vincoli. Per esempio, se un'azione specifica SET DEFAULT, ma il valore di default non soddisfa la chiave esterna, l'operazione fallirà.

Analogous to ON DELETE there is also Analogamente a ON DELETE c'è anche ON UPDATE, che è invocata quando una colonna referenziata viene cambiata (aggiornata). Le azioni possibili sono le stesse.

Dato che un DELETE di una riga da una tabella referenziata o un UPDATE di una colonna referenziata richiederanno una scansione della tabella referente per la ricerca delle righe che soddisfano il vecchio valore, spesso è una buona idea indicizzare le colonne referenti. Siccome questo non è sempre necessario, e ci sono molte scelte disponibili su come indicizzare, la dichiarazione di una chiave esterna non crea automaticamente un indice sulla colonna referente.

Maggiori informazioni circa l'aggiornamento e la cancellazione di dati si trovano in Capitolo 6, Manipolazione dei dati.

Infine, dobbiamo menzionare che una chiave esterna deve referenziare colonne che siano o chiavi primarie o vincolate unique. Se la chiave esterna referenzia un vincolo unique, ci sono alcune possibilità aggiuntive su come i valori null sono combinati. Questi sono spiegati nella documentazione riguardante CREATE TABLE(7).

5.3.6. Vincoli Exclusion

I vincoli Exclusion assicurano che se qualsiasi coppia di righe viane comparata sulle colonne specificate o su espressioni che usano gli operatori specificati, almeno una di queste comparazioni ritornerà false o null. La sintassi è:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

Vedi anche CREATE TABLE ... CONSTRAINT ... EXCLUDE per maggiori dettagli.

Aggiungere un vincolo exclusion creerà automaticamente un indice del tipo specificato nella dichiarazione del vincolo.

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Definizione dei dati > Vincoli
PrecedenteValori di DefaultColonne di SistemaSuccessivo