Applicare l'integrità dei dati con vincoli

I vincoli e gli oggetti sequenza sono scelte di progettazione che impediscono problemi di dati prima che si verifichino. Un vincolo di chiave esterna mancante indica che i record orfani potrebbero essere già presenti nel database. L'aggiunta di oggetti sequenza in un secondo momento per sostituire le colonne Identity richiede modifiche in tutte le applicazioni. Il codice dell'applicazione può convalidare i dati, ma gli utenti possono ignorarli tramite importazioni bulk, query dirette o nuove applicazioni che ignorano la convalida.

I vincoli del database applicano le regole a livello di motore, quindi si applicano sempre. Le decisioni prese durante la progettazione, ad esempio le regole da applicare nel database e se usare colonne o sequenze Identity, influiscono sulla qualità dei dati per la durata dell'applicazione.

Comprendere quando usare i vincoli

I problemi di qualità dei dati sono costosi. La scarsa qualità dei dati comporta decisioni aziendali errate, integrazioni non riuscite e violazioni della conformità. A differenza della convalida a livello di applicazione che può essere incoerente in diverse applicazioni che accedono allo stesso database, i vincoli applicano regole a livello di motore di database in cui non possono essere ignorati dal codice dell'applicazione, dalle query ad hoc, dagli script SQL diretti o dalle importazioni bulk. Ogni INSERToperazione , UPDATEe DELETE deve soddisfare tutti i vincoli definiti prima che il motore di database esemetta la modifica.

Applicare vincoli di database

I vincoli impediscono problemi di qualità dei dati prima di danneggiare il database. La tabella seguente illustra in che modo ogni tipo di vincolo risolve problemi di integrità dei dati specifici:

Si consideri una società di vendita al dettaglio che non ha definito un vincolo univoco nella colonna di posta elettronica del cliente. Nel corso del tempo, gli stessi clienti sono stati registrati più volte con indirizzi di posta elettronica identici. Quando il marketing ha inviato campagne promozionali, alcuni clienti hanno ricevuto tre copie dello stesso messaggio di posta elettronica, aumentando i costi e danneggiando la fiducia dei clienti. L'aggiunta UNIQUE (EmailAddress) alla definizione della tabella avrebbe impedito l'inserimento di questi duplicati.

I vincoli applicano le regole a livello del motore di database, garantendo la qualità dei dati indipendentemente dal modo in cui i dati vengono immessi nel sistema. La convalida dell'applicazione può essere ignorata, varia in base all'applicazione ed è più difficile da gestire. I vincoli di database vengono sempre applicati, centralizzati e forniscono una fonte di verità.

I vincoli garantiscono la qualità e la coerenza dei dati a livello di database.

Usare i vincoli di chiave primaria

I vincoli di chiave primaria garantiscono dati univoci e applicano l'integrità delle entità. Quando si specifica un vincolo di chiave primaria, il motore di database crea automaticamente un indice univoco per le colonne chiave primaria. Una tabella può contenere un solo vincolo di chiave primaria e tutte le colonne definite all'interno di un vincolo di chiave primaria devono essere definite come NOT NULL.

È possibile creare una chiave primaria usando il PRIMARY KEY vincolo . Ecco un esempio:

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    EmailAddress NVARCHAR(100) NOT NULL
);

Usare vincoli di chiave esterna

I vincoli di chiave esterna applicano l'integrità referenziale controllando i dati che possono essere archiviati nella tabella di chiavi esterne. Un vincolo di chiave esterna impedisce modifiche ai dati nella tabella della chiave primaria se tali modifiche invalidano il collegamento ai dati nella tabella di chiave esterna.

È possibile definire azioni referenziali a cascata, ad esempio CASCADE, SET NULL o SET DEFAULT, per specificare cosa accade quando un utente tenta di eliminare o aggiornare una chiave a cui puntano delle chiavi esterne esistenti. Anche se la creazione manuale di un indice nelle colonne di chiave esterna non è necessaria, è spesso utile perché le colonne chiave esterna vengono spesso usate nei criteri di join.

È possibile creare una chiave esterna usando il FOREIGN KEY vincolo con una REFERENCES clausola . Ecco un esempio:

CREATE TABLE Order (
    OrderID INT PRIMARY KEY IDENTITY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Usare vincoli univoci

I vincoli univoci assicurano che non vengano immessi valori duplicati in colonne specifiche che non partecipano a una chiave primaria. A differenza dei vincoli PRIMARY KEY, i vincoli UNIQUE supportano il valore NULL. Tuttavia, come per qualsiasi valore che fa parte di un vincolo UNIQUE, è consentito un solo valore Null per colonna. Il motore di database crea automaticamente un indice non cluster univoco per applicare il requisito di univocità.

È possibile creare un vincolo univoco usando la UNIQUE parola chiave . Ecco un esempio:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    SKU NVARCHAR(50) UNIQUE,
    ProductName NVARCHAR(100)
);

Usare vincoli CHECK

I vincoli Check applicano l'integrità del dominio limitando i valori accettati da una o più colonne. È possibile creare un CHECK vincolo con qualsiasi espressione logica che restituisce TRUE o FALSE in base agli operatori logici. È possibile applicare più CHECK vincoli a una singola colonna o applicare un singolo CHECK vincolo a più colonne.

Poiché i valori Null valutano a UNKNOWN, la loro presenza nelle espressioni potrebbe modificare un vincolo. Ad esempio, un vincolo MyColumn = 10 su una INT colonna consente NULL comunque di essere inserito perché NULL non restituisce FALSE.

È possibile creare un vincolo CHECK usando la CHECK parola chiave con un'espressione logica. Ecco un esempio:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE,
    Salary DECIMAL(10,2),
    CHECK (Salary >= 20000),
    CHECK (HireDate <= GETDATE())
);

Usare i vincoli predefiniti

I vincoli predefiniti forniscono valori predefiniti quando non viene specificato alcun valore durante INSERT le operazioni. Quando si lavora con i progetti di database, è consigliabile creare vincoli con nomi espliciti anziché consentire nomi generati dal sistema, che differiscono tra gli ambienti.

È possibile creare un vincolo DEFAULT usando la DEFAULT parola chiave . Ecco un esempio:

CREATE TABLE Activity (
    ActivityID INT PRIMARY KEY IDENTITY,
    Description NVARCHAR(200),
    CreatedDate DATETIME2 CONSTRAINT DF_Activity_CreatedDate DEFAULT GETUTCDATE(),
    IsActive BIT CONSTRAINT DF_Activity_IsActive DEFAULT 1
);

Usare gli oggetti sequenza

Un oggetto sequenza è un oggetto associato a schema definito dall'utente che genera una sequenza di valori numerici in base alla specifica con cui è stata creata la sequenza. A differenza delle colonne Identity, le sequenze non sono associate a tabelle specifiche. Le applicazioni fanno riferimento a un oggetto sequenza per recuperare il valore successivo e la relazione tra sequenze e tabelle è controllata dall'applicazione.

Le colonne Identity funzionano correttamente quando è necessaria la numerazione automatica per una singola tabella. Tuttavia, sono limitati a quella singola tabella. Non è possibile condividere i numeri tra più tabelle, ottenere il valore successivo prima di inserire una riga o reimpostare facilmente il contatore. Gli oggetti sequenza risolvono questi problemi generando numeri indipendentemente da qualsiasi tabella.

Comprendere quando usare le sequenze

Utilizzare sequenze anziché colonne di identità negli scenari seguenti:

• Serie di numeri condivisi : l'applicazione richiede la condivisione di una singola serie di numeri tra più tabelle o più colonne all'interno di una tabella.
• Serie di numeri ciclica : l'applicazione deve riavviare la serie numerica quando viene raggiunto un numero specificato. Dopo che sono stati assegnati valori da 1 a 10, ad esempio, viene iniziata di nuovo l'assegnazione di valori da 1 a 10.
• Valori di sequenza ordinati : l'applicazione richiede l'ordinamento dei valori di sequenza in base a un altro campo. La NEXT VALUE FOR funzione può applicare la OVER clausola , che garantisce che i valori restituiti vengano generati nell'ordine della ORDER BY clausola .
• Riservare più numeri : un'applicazione deve riservare più numeri sequenziali contemporaneamente. La richiesta di valori di identità potrebbe comportare gap se sono stati emessi contemporaneamente numeri per altri processi. La chiamata sp_sequence_get_range recupera diversi numeri nella sequenza contemporaneamente.
• Specifica modificabile : è necessario modificare la specifica della sequenza, ad esempio il valore di incremento, dopo la creazione.

Gli oggetti sequenza possono offrire maggiore flessibilità rispetto alle colonne Identity:

Usare una colonna Identity quando è necessaria una semplice chiave primaria autoincrementante per una singola tabella e non è necessario condividere la stessa serie numerica tra più tabelle o ottenere il valore successivo prima di inserire la riga.

Usare una sequenza quando l'applicazione richiede un numero prima dell'inserimento, deve condividere una singola serie tra più tabelle, riavviare la serie numerica quando viene raggiunto un numero specificato oppure deve riservare più numeri sequenziali contemporaneamente.

Comprendere le limitazioni della sequenza

A differenza delle colonne Identity, i valori di sequenza non vengono protetti automaticamente dopo l'inserimento in una tabella. Inoltre, l'univocità non viene applicata automaticamente per i valori di sequenza. Se i valori di sequenza in una tabella devono essere univoci, creare un vincolo univoco nella colonna.

I numeri di sequenza vengono generati esternamente all'ambito della transazione corrente. Vengono utilizzati sia in caso di commit che di rollback della transazione che utilizza il numero di sequenza.

È possibile creare un oggetto sequenza usando l'istruzione CREATE SEQUENCE con parametri facoltativi per inizio, incremento e intervallo. Ecco un esempio:

-- Create sequence
CREATE SEQUENCE OrderNumber 
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    NO CYCLE;

-- Use sequence in INSERT with NEXT VALUE FOR function
INSERT INTO Order (OrderID, CustomerID, OrderNumber, OrderDate)
VALUES (1, 100, NEXT VALUE FOR OrderNumber, GETDATE());

-- Get next value before INSERT
DECLARE @NextOrderNum INT = NEXT VALUE FOR OrderNumber;
SELECT @NextOrderNum;

-- Get multiple sequence numbers at once for batch processing
DECLARE @FirstSeq INT, @LastSeq INT;
EXEC sp_sequence_get_range 
    @sequence_name = N'OrderNumber',
    @range_size = 100,
    @range_first_value = @FirstSeq OUTPUT,
    @range_last_value = @LastSeq OUTPUT;

-- Reset sequence
ALTER SEQUENCE OrderNumber RESTART WITH 1000;

In questo esempio viene creata una sequenza denominata OrderNumber che inizia a 1000, incrementa di 1 e si arresta a 999999 senza tornare indietro. La funzione NEXT VALUE FOR recupera il numero disponibile successivo, sia inline durante un'istruzione INSERT o assegnato a una variabile prima dell'inserimento, quando l'applicazione deve fare riferimento al valore in primo luogo. Per le operazioni batch che richiedono più numeri sequenziali contemporaneamente, sp_sequence_get_range riserva un blocco di 100 numeri, restituendo i primi e gli ultimi valori nell'intervallo. L'istruzione ALTER SEQUENCE reimposta la sequenza su 1000 quando necessario.

I vincoli sono decisioni architetturali che impediscono problemi prima che si verifichino. Un vincolo mancante CHECK consente ai dati non validi di danneggiare automaticamente il database. La scelta delle colonne Identity quando è necessaria la numerazione tra tabelle forza le soluzioni alternative a livello di applicazione. I vincoli definiti a livello di database proteggono la qualità dei dati indipendentemente dall'applicazione, dallo strumento o dallo script che accede al database. Queste decisioni modellano le garanzie di integrità dei dati per la durata dell'applicazione.