SQLShack (Italiano)
Nel precedente articolo Vincoli SQL Server comunemente usati: NON NULL, CHIAVE UNIVOCA e PRIMARIA, abbiamo descritto, in dettaglio, i primi tre tipi di vincoli SQL Server; NON NULL, CHIAVE UNIVOCA e PRIMARIA. In questo articolo, discuteremo gli altri tre vincoli; CHIAVE ESTERNA, CONTROLLO e DEFAULT descrivendo ciascuno brevemente e fornendo esempi pratici.,
Vincolo di CHIAVE ESTERNA
Una chiave esterna è una chiave di database che viene utilizzata per collegare due tabelle insieme. Il vincolo della CHIAVE ESTERNA identifica le relazioni tra le tabelle del database facendo riferimento a una colonna, o a un insieme di colonne, nella tabella Figlio che contiene la chiave esterna, alla colonna della chiave PRIMARIA o a un insieme di colonne, nella tabella padre.
La relazione tra le tabelle figlio e padre viene mantenuta controllando l’esistenza dei valori della CHIAVE ESTERNA della tabella figlio nella CHIAVE PRIMARIA della tabella padre di riferimento prima di inserire questi valori nella tabella figlio., In questo modo, il vincolo della CHIAVE ESTERNA, nella tabella figlio che fa riferimento alla CHIAVE PRIMARIA nella tabella padre, imporrà l’integrità referenziale del database. L’integrità referenziale garantisce che la relazione tra le tabelle del database sia preservata durante il processo di inserimento dei dati. Ricordiamo che il vincolo della CHIAVE PRIMARIA garantisce che nessun valore NULLO o duplicato per la colonna o le colonne selezionate verrà inserito in quella tabella, applicando l’integrità dell’entità per quella tabella., L’integrità dell’entità applicata dalla CHIAVE PRIMARIA e l’integrità referenziale applicata dalla CHIAVE ESTERNA formano insieme l’integrità della chiave.
Il vincolo della CHIAVE ESTERNA differisce dal vincolo della CHIAVE PRIMARIA in quanto è possibile creare solo una CHIAVE PRIMARIA per ogni tabella, con la possibilità di creare più vincoli di CHIAVE ESTERNA in ogni tabella facendo riferimento a più tabelle padre. Un’altra differenza è che la CHIAVE ESTERNA consente di inserire valori NULL se non esiste un vincolo NOT NULL definito su questa chiave, ma la CHIAVE PRIMARIA non accetta valori NULL.,
Il vincolo della CHIAVE ESTERNA offre anche la possibilità di controllare quale azione verrà intrapresa quando il valore di riferimento nella tabella padre viene aggiornato o eliminato, utilizzando le clausole ON UPDATE e ON DELETE. Le azioni supportate che possono essere eseguite quando si eliminano o si aggiornano i valori della tabella padre includono:
- NESSUNA AZIONE: quando le clausole ON UPDATE o ON DELETE sono impostate su NESSUNA AZIONE, l’operazione di aggiornamento o eliminazione eseguita nella tabella padre fallirà con un errore.,
- CASCADE: Impostando le clausole ON UPDATE o ON DELETE su CASCADE, la stessa azione eseguita sui valori di riferimento della tabella padre si rifletterà sui valori correlati nella tabella figlio. Ad esempio, se il valore di riferimento viene eliminato nella tabella padre, vengono eliminate anche tutte le righe correlate nella tabella figlio.
- SET NULL: Con questa opzione ON UPDATE e ON DELETE, se i valori di riferimento nella tabella padre vengono eliminati o modificati, tutti i valori correlati nella tabella figlio vengono impostati su valore NULL.,
- IMPOSTA PREDEFINITO: utilizzando l’opzione IMPOSTA PREDEFINITO delle clausole ON UPDATE e ON DELETE specifica che, se i valori di riferimento nella tabella padre vengono aggiornati o eliminati, i valori correlati nella tabella figlio con colonne di CHIAVI ESTERNE verranno impostati sul valore predefinito.
È possibile aggiungere il vincolo della chiave ESTERNA durante la definizione della colonna utilizzando l’istruzione CREATE TABLE T-SQL o aggiungerlo dopo la creazione della tabella utilizzando l’istruzione ALTER TABLE T-SQL. Creeremo due nuove tabelle per comprendere la funzionalità del vincolo della chiave ESTERNA., La prima tabella fungerà da tabella padre con la colonna ID definita come colonna CHIAVE PRIMARIA. La seconda tabella fungerà da tabella figlio, con la colonna ID definita come colonna della CHIAVE ESTERNA che fa riferimento alla colonna ID nella tabella padre.,> ID INT PRIMARY KEY,
Dopo aver creato le due tabelle, inserire tre record alla tabella padre, e due record della tabella figlio, utilizzando le seguenti istruzioni INSERT:
Il risultato mostra che i tre dischi sono correttamente inserite nella tabella padre., Il primo record che abbiamo provato a inserire nella tabella figlio viene inserito senza alcun errore poiché il valore ID di 1 esiste già nella tabella padre.,perché il valore di ID di 4 non esiste nella tabella padre, e a causa del vincolo di CHIAVE esterna, non sarà in grado di inserire un valore di ID della tabella figlio che non esiste nella tabella padre:
Controllare le tabelle padre e figlio, di contenuti, vedrai che un solo record viene inserito nella tabella figlio, come potete vedere qui sotto:
Come abbiamo fatto a non ricordare il nome del vincolo FOREIGN KEY durante la creazione della tabella figlio, SQL Server assegna un nome univoco che possiamo recuperare da INFORMATION_SCHEMA.,TABLE_CONSTRAINTS system view using the following query:
|
1
2
3
4
5
6
7
8
|
SELECT CONSTRAINT_NAME,
TABLE_SCHEMA ,
TABLE_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.,onstraintDemoChild
AGGIUNGERE il VINCOLO di FK__ConstraintDe__ID
FOREIGN KEY (ID) RIFERIMENTI ConstraintDemoParent(ID);
|
L’operazione ha esito negativo, come il valore di ID di 4 non esiste nella tabella padre e interruzioni di integrità referenziale tra il bambino e il genitore tabelle, come si vede nella seguente messaggio di errore:
Per essere in grado di creare il vincolo di CHIAVE esterna della tabella figlio, dobbiamo eliminare il conflitto prima con la cancellazione o l’aggiornamento del record., Here we will try to modify the ID value with the following UPDATE command:
|
1
2
3
|
UPDATE ., SET ID =2 WHERE ID = 4
|
Ora il vincolo di CHIAVE esterna verrà creato con successo senza alcun errore, come mostrato di seguito:
UN vincolo di CHIAVE esterna può essere definito con l’aiuto di strumento SQL Server Management Studio. Fare clic destro sulla tabella richiesta e selezionare l’opzione di progettazione.,> Dalla finestra visualizzata, si può facilmente specificare il nome di un vincolo di CHIAVE esterna, le tabelle e le colonne che parteciperanno in quella relazione, cliccando sulla Specifica di Tabelle E Colonne, se la verifica dei dati esistenti è richiesto, e, infine, l’azione che viene eseguita quando il riferimento è il record viene eliminato o modificato la tabella principale, come mostrato di seguito:
Vincolo di CONTROLLO
UN vincolo di CONTROLLO è definito su una colonna o un insieme di colonne per limitare l’intervallo di valori che possono essere inseriti in queste colonne, utilizzando una condizione predefinita., Il vincolo di CONTROLLO entra in azione per valutare i valori inseriti o modificati, dove il valore che soddisfa la condizione verrà inserito nella tabella, altrimenti l’operazione di inserimento verrà scartata. È consentito specificare più vincoli di controllo per la stessa colonna.
Definire la condizione di vincolo di CONTROLLO è in qualche modo simile alla scrittura della clausola WHERE di una query, utilizzando i diversi operatori di confronto, come AND, OR, BETWEEN, IN, LIKE and IS NULL per scrivere la sua espressione booleana che restituirà TRUE, FALSE o UNKNOWN., Il vincolo di CONTROLLO restituirà un valore SCONOSCIUTO quando è presente un valore NULL nella condizione. Il vincolo di CONTROLLO viene utilizzato principalmente per applicare l’integrità del dominio limitando i valori inseriti a quelli che seguono i valori definiti, l’intervallo o le regole di formato.
Creiamo una nuova tabella semplice che ha tre colonne; la colonna ID che viene considerata come la CHIAVE PRIMARIA di quella tabella, Nome e Stipendio. Un vincolo di CONTROLLO è definito nella colonna Stipendio per assicurarsi che non vengano inseriti valori zero o negativi in quella colonna.,v>6
Se si esegue le seguenti tre istruzioni INSERT:
dalla generato risultato che il primo record è stato inserito con nessun errore come fornito Stipendio valore soddisfa la condizione controllo.,date Stipendio valori non soddisfano il vincolo di CONTROLLO della condizione a causa di inserire zero o negativo Stipendio valori, come si può vedere nella seguente messaggio di errore:
Controllare la tabella dei dati mostra che solo la prima riga che superato il vincolo di CONTROLLO della condizione, sarà inserita la tabella come di seguito:
Abbiamo accennato in precedenza in questo articolo, che il vincolo di CONTROLLO che consente di inserire i valori NULL se l’hanno partecipato colonne consentono di NULL NULL valori sono valutati come SCONOSCIUTO senza generare alcun errore., Questo è chiaro dal record sottostante che viene inserito correttamente, sebbene il valore fornito della colonna Stipendio sia NULL:
Se si esamina la definizione del vincolo di CONTROLLO nella precedente istruzione CREATE TABLE, si vedrà che non abbiamo menzionato il nome del vincolo definito. In questo caso, SQL Server assegnerà un nome univoco per quel vincolo che può essere mostrato interrogando INFORMATION_SCHEMA.TABLE_CONSTRAINTS vista di sistema per la tabella ConstraintDemo4., Il risultato sarà come mostrato di seguito:
Il vincolo di CONTROLLO può essere eliminato utilizzando l’istruzione ALTER TABLE T-SQL.,onstraint now, with the following ALTER DATABASE … ADD CONSTRAINT T-SQL statement:
|
1
2
3
4
5
|
ALTER TABLE ConstraintDemo4
ADD CONSTRAINT CK__Constrain__Salar
CHECK (Salary>0);
|
Adding the CHECK constraint will fail., Questo è dovuto al fatto che, mentre SQL Server verifica la già esistente di dati per la VERIFICA del vincolo di condizione, di uno o più valori che non soddisfano la condizione di vincolo sono trovato, come si può vedere nella seguente messaggio di errore:
Per essere in grado di definire il vincolo CHECK di nuovo, si dovrebbe modificare o cancellare i dati, che impedisce che il vincolo venga creato.,de484761″>
Ora, il vincolo di CONTROLLO può essere definito con nessun problema, come illustrato di seguito:
SQL Server consente di disattivare il CONTROLLO di vincolo per i casi speciali, ad esempio l’inserimento di enormi quantità di dati, senza curarsi di conoscere la condizione di vincolo per scopi di test, o a causa del cambiamento della logica di business., Questo caso è valido solo per i vincoli CHECK e FOREIGN KEY che è possibile disabilitare temporaneamente.,scritto il nome del vincolo, come il comando T-SQL di seguito:
|
1
2
3
|
ALTER TABLE ConstraintDemo4 NOCHECK VINCOLO di TUTTI
|
Dal vincolo CHECK definizione, è possibile specificare che il vincolo dovrà essere creata senza controllo righe esistenti, ma il vincolo viene contrassegnato come non attendibile., può consentire a tutti i vincoli CHECK, tutto in una volta,utilizzando il comando T-SQL di seguito:
|
1
2
3
|
ALTER TABLE ConstraintDemo4 VINCOLO di CONTROLLO di TUTTI
|
Pur consentendo il CONTROLLO precedente vincolo, vedrete che SQL Server non si lamentano i dati non controllati, che spezza i vincoli condizione., In questo caso, l’integrità del dominio dei dati non viene mantenuta.
Per risolvere questo problema, il comando DBCC CHECKCONSTRAINTS può essere facilmente utilizzato per identificare i dati che violano la condizione di vincolo in una tabella o vincolo specificato, tenendo in considerazione di non eseguire tale comando nelle ore di punta, in quanto influenzerà le prestazioni di SQL Server a causa di non utilizzare uno snapshot del database.,v id=”ff419aa09d”>
Il risultato di mostrare i due di Stipendio valori di rompere il vincolo di CONTROLLO della condizione, come mostrato di seguito:
UN vincolo di CONTROLLO può essere creato anche con l’aiuto di strumento SQL Server Management Studio, facendo clic destro sulla tabella e selezionando l’opzione di Progettazione., Nella visualizzazione della finestra di Progettazione, fare clic destro e scegliere i Vincoli Check opzione come di seguito:
Da Vincoli Check finestra, è possibile specificare il vincolo CHECK nome, di espressione e di se controllo i dati esistenti è necessario, come illustrato di seguito:
Vincolo PREDEFINITO
UN vincolo PREDEFINITO viene utilizzato per fornire un valore predefinito della colonna per le righe inserite, se non viene specificato alcun valore per la colonna nell’istruzione INSERT., Il vincolo predefinito aiuta a mantenere l’integrità del dominio fornendo valori appropriati per la colonna, nel caso in cui l’utente non fornisca un valore per essa. Il valore predefinito può essere un valore costante, un valore di funzione di sistema o NULL.,di seguito:
|
1
2
3
4
|
ALTER TABLE ConstraintDemo5
Aggiungere il Vincolo di DF__Vincolare__Addetto DEFAULT (GETDATE()) PER EmployeeDate
|
Inoltre, il vincolo DEFAULT, possono essere definiti utilizzando SQL Server Management Studio, facendo clic destro sulla tabella e scegliere opzione di Progettazione., Quindi seleziona la colonna a cui assegnerai un valore predefinito sfogliando la finestra delle proprietà della colonna come mostrato di seguito:
Speravo che questo articolo e il precedente abbiano contribuito a spiegare i sei tipi di vincoli di SQL Server. Non esitate a fare tutte le domande nei commenti qui sotto.,solo utilizzato SQL Server Vincoli NOT NULL, UNICO e CHIAVE PRIMARIA
link Utili
- Creare Vincoli Check
- Vincoli
- Default
- Creare Relazioni di Chiave esterna
- VERIFICARE i Vincoli
- Autore
- Post Recenti
È un esperto di soluzioni certificate Microsoft in gestione e analisi dei dati, Microsoft Certified Solution Associate in amministrazione e sviluppo di database SQL, Azure Developer Associate e Microsoft Certified Trainer.
Inoltre, sta contribuendo con i suoi suggerimenti SQL in molti blog.,
Visualizza tutti i messaggi di Ahmad Yaseen
- Come per monitorare Azure Dati di Fabbrica – il 15 gennaio 2021
- Uso del Controllo del codice Sorgente in Azzurro i Dati di Fabbrica – gennaio 12, 2021
- Utilizzo di Modelli di Azure Dati di Fabbrica – 8 gennaio 2021