Creare relazioni di chiave esterna

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo descrive come creare relazioni di chiavi esterne in SQL Server usando SQL Server Management Studio o Transact-SQL. Una relazione tra due tabelle consente di stabilire un'associazione tra le righe di una tabella e le righe di un'altra tabella.

Autorizzazioni

Per la creazione di una nuova tabella con una chiave esterna è richiesta l'autorizzazione CREATE TABLE per il database e l'autorizzazione ALTER SCHEMA per lo schema in cui viene creata la tabella.

Per la creazione di una chiave esterna in una tabella esistente è richiesta l'autorizzazione ALTER TABLE per la tabella.

Limiti

  • Un vincolo di chiave esterna non deve necessariamente essere collegato solo a un vincolo di chiave primaria in un'altra tabella. È anche possibile definire le chiavi esterne per fare riferimento alle colonne di un vincolo UNIQUE in un'altra tabella.

  • I valori diversi da NULL immessi nella colonna di un vincolo FOREIGN KEY devono essere presenti nella colonna a cui viene fatto riferimento. In caso contrario, viene restituito un messaggio di errore di violazione della chiave esterna. Per assicurarsi che tutti i valori di un vincolo di chiave esterna composita vengano verificati, specificare NOT NULL in tutte le colonne coinvolte.

  • I vincoli FOREIGN KEY possono fare riferimento solo a tabelle all'interno dello stesso database sullo stesso server. L'integrità referenziale tra database diversi deve essere implementata tramite trigger. Per altre informazioni, vedere CREATE TRIGGER (Transact-SQL).

  • I vincoli FOREIGN KEY possono fare riferimento a un'altra colonna nella stessa tabella. Questo tipo di vincolo viene definito autoreferenziale.

  • Un vincolo FOREIGN KEY specificato a livello di colonna può includere una sola colonna di riferimento. Il tipo di dati di tale colonna deve essere uguale al tipo di dati della colonna in cui viene definito il vincolo.

  • Un vincolo FOREIGN KEY specificato a livello di tabella deve includere lo stesso numero di colonne di riferimento di quelle presenti nell'elenco di colonne del vincolo. Il tipo di dati di ogni colonna di riferimento deve inoltre essere uguale a quello della colonna corrispondente nell'elenco di colonne.

  • Il motore di database non ha un limite predefinito per il numero di vincoli FOREIGN KEY che una tabella può contenere e che fanno riferimento ad altre tabelle. Il motore di database inoltre non limita il numero di vincoli FOREIGN KEY di proprietà di altre tabelle che fanno riferimento a una tabella specifica. Tuttavia, il numero effettivo di vincoli FOREIGN KEY che è possibile usare è limitato dalla configurazione hardware e dalla progettazione del database e dell'applicazione. Una tabella può fare riferimento a un massimo di 253 altre tabelle e colonne come chiavi esterne (riferimenti in uscita). SQL Server 2016 (13.x) e versioni successive incrementa da 253 e 10.000 il limite per il numero di altre tabelle e colonne che possono fare riferimento alle colonne in una singola tabella (riferimenti in ingresso). (richiede almeno il livello di compatibilità 130). All'incremento vengono applicate le seguenti restrizioni:

    • I riferimenti di chiave esterna maggiori di 253 sono supportati per le operazioni DELETE e UPDATE DML. Le operazioni MERGE non sono supportate.

    • Una tabella con un riferimento di chiave esterna a se stessa è comunque limitata a 253 riferimenti di chiave esterna.

    • I riferimenti di chiave esterna maggiori di 253 non sono attualmente disponibili per gli indici columnstore o le tabelle ottimizzate per la memoria.

  • I vincoli FOREIGN KEY non vengono applicati nelle tabelle temporanee.

  • Se si definisce una chiave esterna su una colonna di tipo CLR definito dall'utente, è necessario che l'implementazione del tipo supporti l'ordinamento binario. Per altre informazioni, vedere Tipi CLR definiti dall'utente.

  • Una colonna di tipo varchar(max) può far parte di un vincolo FOREIGN KEY solo se anche la chiave primaria a cui fa riferimento è definita come tipo varchar(max).

Creare una relazione di chiave esterna in Progettazione tabelle

Usare SQL Server Management Studio

  1. In Esplora oggetti fare clic con il pulsante destro del mouse sulla tabella che si troverà sul lato chiave esterna della relazione e selezionare Progetta.

    La tabella si apre in Creare e aggiornare tabelle di database.

  2. Selezionare Relazioni dal menu Progettazione tabelle. (Vedere il menu Progettazione tabelle nell'intestazione o fare clic con il pulsante destro del mouse nello spazio vuoto della definizione della tabella, quindi selezionare Relazioni....)

  3. Nella finestra di dialogo Relazioni chiavi esterne selezionare Aggiungi.

    La relazione verrà visualizzata nell'elenco Relazione selezionata con un nome specificato dal sistema nel formato FK_<tablename>_<tablename>, dove il primo tablename è il nome della tabella di chiave esterna e il secondo tablename è il nome della tabella di chiave primaria. Si tratta semplicemente di una comune convenzione di denominazione predefinita per il campo (Nome) dell'oggetto chiave esterna.

  4. Selezionare la relazione nell'elenco Relazione selezionata.

  5. Selezionare Specifica tabelle e colonne nella griglia a destra e quindi il pulsante con i puntini di sospensione (...) a destra della proprietà.

  6. Nella finestra di dialogo Tabelle e colonne selezionare dall'elenco a discesa Chiave primaria la tabella che si troverà sul lato chiave primaria della relazione.

  7. Nella griglia sotto la finestra di dialogo, selezionare le colonne che contribuiscono alla chiave primaria della tabella. Nella cella adiacente a destra di ogni colonna selezionare la corrispondente colonna chiave esterna della tabella di chiave esterna.

    Progettazione tabelle suggerisce automaticamente un nome da assegnare alla relazione. Per specificare un nome diverso, modificare il contenuto della casella di testo Nome relazione .

  8. Scegliere OK per creare la relazione.

  9. Chiudere la finestra Progettazione tabelle e salvare le modifiche per rendere effettiva la modifica della relazione di chiave esterna.

Creare una chiave esterna in una nuova tabella

Usare Transact-SQL

L'esempio seguente crea una tabella e definisce un vincolo di chiave esterna nella colonna TempID alla quale fa riferimento la colonna SalesReasonID nella tabella Sales.SalesReason nel database AdventureWorks. Le clausole ON DELETE CASCADE e ON UPDATE CASCADE vengono utilizzate per garantire che le modifiche apportate alla tabella Sales.SalesReason vengano propagate automaticamente alla tabella Sales.TempSalesReason.

CREATE TABLE Sales.TempSalesReason (
    TempID INT NOT NULL,
    Name NVARCHAR(50),
    CONSTRAINT PK_TempSales
        PRIMARY KEY NONCLUSTERED (TempID),
    CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
        REFERENCES Sales.SalesReason(SalesReasonID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Creare una chiave esterna in una tabella esistente

Usare Transact-SQL

L'esempio seguente crea una chiave esterna nella colonna TempID e fa riferimento alla colonna SalesReasonID nella tabella Sales.SalesReason nel database AdventureWorks.

ALTER TABLE Sales.TempSalesReason
ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
REFERENCES Sales.SalesReason (SalesReasonID)
   ON DELETE CASCADE
   ON UPDATE CASCADE;

Contenuto correlato