Microsoft SQL Server
Chiavi esterne

Relazione / vincolo di chiave esterna

Le chiavi esterne consentono di definire la relazione tra due tabelle. Una tabella (principale) deve avere una chiave primaria che identifichi in modo univoco le righe nella tabella. Un'altra tabella (secondaria) può avere il valore della chiave primaria dal genitore in una delle colonne. Il vincolo RIFERIMENTI CHIAVE ESTERA assicura che i valori nella tabella figlio debbano esistere come valore della chiave primaria nella tabella padre.

In questo esempio abbiamo la tabella Società padre con la chiave primaria CompanyId e la tabella Employee figlio con ID della società in cui lavora questo dipendente.

create table Company (
   CompanyId int primary key,
   Name nvarchar(200)
)
create table Employee (
    EmployeeId int,
    Name nvarchar(200),
    CompanyId int
        foreign key references Company(companyId)
)

i riferimenti a chiavi esterne assicurano che i valori inseriti nella colonna Employee.CompanyId siano presenti anche nella colonna Company.CompanyId. Inoltre, nessuno può eliminare la società nella tabella della società se è presente almeno un dipendente con una società corrispondente in una tabella figlio.

La relazione FOREIGN KEY assicura che le righe in due tabelle non possano essere "scollegate".

Mantenimento della relazione tra righe padre / figlio

Supponiamo di avere una riga nella tabella Company con companyId 1. Possiamo inserire una riga nella tabella dei dipendenti che ha companyId 1:

insert into Employee values (17, 'John', 1)

Tuttavia, non possiamo inserire dipendenti con ID azienda non esistente:

insert into Employee values (17, 'John', 111111)

Messaggio 547, livello 16, stato 0, riga 12 L'istruzione INSERT era in conflitto con il vincolo FOREIGN KEY "FK__Employee__Compan__1EE485AA". Il conflitto si è verificato nel database "MyDb", nella tabella "dbo.Company", nella colonna "CompanyId". La dichiarazione è stata chiusa.

Inoltre, non è possibile eliminare la riga padre nella tabella della società finché nella tabella dei dipendenti è presente almeno una riga secondaria che la fa riferimento.

delete from company where CompanyId = 1

Messaggio 547, livello 16, stato 0, riga 14 L'istruzione DELETE è in conflitto con il vincolo REFERENCE "FK__Employee__Compan__1EE485AA". Il conflitto si è verificato nel database "MyDb", nella tabella "dbo.Employee", nella colonna "CompanyId". La dichiarazione è stata chiusa.

La relazione tra le chiavi esterne assicura che le righe dell'azienda e dei dipendenti non siano "scollegate".

Aggiunta di una relazione di chiave esterna sulla tabella esistente

Il vincolo FOREIGN KEY può essere aggiunto su tabelle esistenti che non sono ancora in relazione. Immagina di avere una tabella Company e Employee in cui la colonna CompanyId della tabella Employee non ha una relazione di chiave esterna. L'istruzione ALTER TABLE consente di aggiungere un vincolo di chiave esterna su una colonna esistente che fa riferimento ad altre tabelle e chiavi primarie in tale tabella:

alter table Employee
    add  foreign key (CompanyId) references Company(CompanyId)

Aggiungi chiave esterna sul tavolo esistente

Le colonne FOREIGN KEY con vincolo possono essere aggiunte su tabelle esistenti che non sono ancora in relazione. Immaginiamo di avere tabelle Company e Employee in cui la tabella Employee non ha la colonna CompanyId. L'istruzione ALTER TABLE consente di aggiungere una nuova colonna con il vincolo di chiave esterna che fa riferimento ad altre tabelle e chiavi primarie in tale tabella:

alter table Employee
    add CompanyId int foreign key references Company(CompanyId)

Ottenere informazioni sui vincoli di chiave esterna

La vista di sistema sys.foreignkeys restituisce informazioni su tutte le relazioni con le chiavi esterne nel database:

select name,
 OBJECT_NAME(referenced_object_id) as [parent table],
 OBJECT_NAME(parent_object_id) as [child table],
 delete_referential_action_desc,
 update_referential_action_desc
from sys.foreign_keys