SI APPLICA A: SQL Server (a partire dal 2016) Database SQL di Azure Azure SQL Data Warehouse Parallel Data Warehouse
Le chiavi primarie ed esterne sono due tipi di vincoli che possono essere utilizzati per imporre l'integrità dei dati in Tabelle SQL server. Questi sono importanti oggetti di database.
Questo argomento è descritto nelle sezioni seguenti.
Vincoli di chiave primaria
Vincoli di chiave esterna
Attività correlate
In genere, una tabella ha una colonna o una combinazione di colonne che contiene valori che identificano in modo univoco ogni riga della tabella. Questa colonna, o colonne, è chiamata chiave primaria (PK) della tabella e fornisce l'integrità dell'entità della tabella. I vincoli di chiave primaria sono spesso definiti su una colonna Identity perché garantiscono l'univocità dei dati.
Quando imposti un vincolo di chiave primaria su una tabella, il Motore di database garantisce l'univocità dei dati in base a creazione automatica indice univoco sulle colonne della chiave primaria. Questo indice fornisce inoltre un rapido accesso ai dati quando si utilizza la chiave primaria nelle query. Se un vincolo di chiave primaria è definito su più di una colonna, i valori possono essere duplicati all'interno della stessa colonna, ma ogni combinazione di valori di tutte le colonne nella definizione del vincolo di chiave primaria deve essere univoca.
Come mostrato nella figura seguente, le colonne Codice prodotto E ID del venditore sul tavolo Acquisti.ProdottoVenditore formare un vincolo di chiave primaria composita per la tabella data. Ciò garantisce che ogni riga della tabella Fornitore di prodotti ha una combinazione unica di valori Codice prodotto E ID del venditore. Ciò impedisce l'inserimento di righe duplicate.
Una tabella può avere un solo vincolo di chiave primaria.
La chiave primaria non può avere più di 16 colonne e la lunghezza totale della chiave non può superare i 900 byte.
Un indice formato da un vincolo di chiave primaria non può far sì che il numero di indici in una tabella superi 999 indici non cluster e 1 indice cluster.
Se il vincolo di chiave primaria non specifica se l'indice è cluster o non cluster, viene creato un indice cluster se non ne esiste uno nella tabella.
Tutte le colonne con un vincolo di chiave primaria devono essere definite come non nullable. Se non viene specificato nulla, tutte le colonne con un vincolo di chiave primaria vengono impostate come non nullable.
Se una chiave primaria è definita in una colonna di un tipo di dati CLR definito dall'utente, l'implementazione del tipo deve supportare l'ordinamento binario.
Una chiave esterna (FK) è una colonna o una combinazione di colonne utilizzata per forzare una relazione tra i dati in due tabelle al fine di controllare i dati che possono essere archiviati nella tabella della chiave esterna. Se si fa riferimento a una o più colonne contenenti la chiave primaria per una tabella in una o più colonne di un'altra tabella, viene creata una relazione nel riferimento della chiave esterna tra le due tabelle. Questa colonna diventa una chiave esterna nella seconda tabella.
Ad esempio tavolo Sales.SalesOrderHeader collegato alla tabella Venditore.Venditore utilizzando una chiave esterna perché esiste una relazione logica tra ordini di vendita e venditori. Colonna ID venditore sul tavolo Sales.SalesOrderHeader corrisponde alla colonna della chiave primaria nella tabella venditore. Colonna ID venditore sul tavolo Sales.SalesOrderHeaderè una chiave esterna per la tabella venditore. Stabilendo questa relazione tramite chiave esterna, il valore per ID venditore non può essere inserito nella tabella SalesOrderHeader se non è attualmente nella tabella venditore.
Il numero massimo di tabelle e colonne a cui una tabella può fare riferimento come chiavi esterne (collegamenti in uscita) è 253. SQL Server 2016 aumenta il limite del numero di altre tabelle e colonne che possono fare riferimento a colonne nella stessa tabella (collegamenti in entrata) da 253 fino a 10.000. (Richiede un livello di compatibilità di almeno 130.) L'aumento ha le seguenti limitazioni:
Il superamento di 253 riferimenti di chiavi esterne è supportato solo per le operazioni DML DELETE. Le operazioni UPDATE e MERGE non sono supportate.
Oltre 253 riferimenti di chiavi esterne non sono attualmente disponibili per indici columnstore, tabelle ottimizzate per la memoria, database Stretch o tabelle di chiavi esterne partizionate.
A differenza dei vincoli di chiave primaria, la creazione di un vincolo di chiave esterna non crea automaticamente un indice corrispondente. Tuttavia, è spesso necessario creare manualmente un indice su una chiave esterna per i seguenti motivi:
Le colonne chiave esterna vengono spesso utilizzate nei criteri di join quando vengono utilizzate insieme nelle query di dati da tabelle correlate. Lo fa mappando una o più colonne in un vincolo di chiave esterna in una tabella a una o più colonne chiave primarie o univoche in un'altra tabella. Un indice consente al Motore di database di trovare rapidamente i dati correlati in una tabella di chiavi esterne. Tuttavia, la creazione di un indice è facoltativa. I dati di due tabelle correlate possono essere combinati anche se non sono definiti vincoli di chiave primaria o chiave esterna tra le tabelle, ma una relazione di chiave esterna tra due tabelle mostra che le due tabelle sono ottimizzate per essere utilizzate insieme in una query in cui le chiavi sono utilizzati come criteri.
I vincoli di chiave esterna nelle tabelle correlate controllano le modifiche ai vincoli di chiave primaria.
Lo scopo principale di un vincolo di chiave esterna è controllare i dati che possono essere archiviati nella tabella della chiave esterna, ma questo vincolo controlla anche la modifica dei dati nella tabella della chiave primaria. Ad esempio, quando si elimina una riga per un responsabile delle vendite da una tabella Venditore.Venditore Il cui ID viene utilizzato negli ordini cliente nella tabella Sales.SalesOrderHeader, l'integrità referenziale delle due tabelle verrà violata. Ordini di vendita del gestore remoto in una tabella SalesOrderHeader diventano non validi senza un collegamento ai dati nella tabella venditore.
Il vincolo di chiave esterna impedisce che si verifichi questa situazione. Un vincolo applica l'integrità referenziale nel modo seguente: proibisce la modifica dei dati nella tabella della chiave primaria se tale modifica invaliderebbe il riferimento nella tabella della chiave esterna. Se, quando si tenta di eliminare una riga nella tabella della chiave primaria o di modificare il valore di questa chiave, si scopre che il valore eliminato o modificato della chiave primaria corrisponde a certo valore in un vincolo di chiave esterna in un'altra tabella, non verrà intrapresa alcuna azione. Per aggiornare o eliminare correttamente una riga con un vincolo di chiave esterna, devi prima eliminare i dati della chiave esterna nella tabella della chiave esterna o modificare i dati nella tabella della chiave esterna che collega la chiave esterna a un'altra chiave primaria.
È possibile utilizzare vincoli di integrità referenziale a catena per definire le azioni che verranno eseguite da Motore di database quando un utente tenta di eliminare o aggiornare una chiave a cui puntano ancora chiavi esterne. È possibile definire le seguenti azioni a cascata.
NESSUNA AZIONE
Il Motore di database genera un errore e quindi viene eseguito il rollback dell'operazione di eliminazione o aggiornamento sulla riga nella tabella padre.
CASCATA
Le righe corrispondenti vengono aggiornate o rimosse dalla tabella di riferimento se stringa data viene aggiornato o rimosso dalla tabella padre. Il valore CASCADE non può essere specificato se la colonna è di tipo timestamp fa parte di una chiave esterna o di riferimento. L'azione ON DELETE CASCADE non può essere specificata su una tabella in cui è definito un trigger INSTEAD OF DELETE. La clausola ON UPDATE CASCADE non può essere utilizzata su tabelle che dispongono di trigger INSTEAD OF UPDATE.
IMPOSTA NULLA
Tutti i valori che compongono una chiave esterna vengono impostati su NULL quando la riga corrispondente nella tabella padre viene aggiornata o eliminata. Le colonne della chiave esterna devono essere nullable per applicare questa restrizione. Non può essere impostato per le tabelle con trigger INSTEAD OF UPDATE definiti.
IMPOSTA DEFAULT
Tutti i valori che compongono una chiave esterna vengono impostati sul loro valore predefinito quando la riga corrispondente nella tabella padre viene eliminata o aggiornata. Per rispettare questo vincolo, tutte le colonne di chiavi esterne devono avere definizioni predefinite. Se la colonna ammette valori Null e non è definito in modo esplicito alcun valore predefinito, il valore predefinito della colonna diventa NULL. Non può essere impostato per le tabelle con trigger INSTEAD OF UPDATE definiti.
Le parole chiave CASCADE, SET NULL, SET DEFAULT e NO ACTION possono essere combinate in tabelle con riferimenti reciproci. Se il Motore di database rileva la parola chiave NO ACTION, interromperà ed eseguirà il rollback delle operazioni CASCADE, SET NULL e SET DEFAULT associate. Se un'istruzione DELETE contiene una combinazione delle parole chiave CASCADE, SET NULL, SET DEFAULT e NO ACTION, tutte le operazioni CASCADE, SET NULL e SET DEFAULT vengono eseguite prima che il Motore di database cerchi un'operazione NO ACTION.
Le azioni referenziali a cascata attivano trigger AFTER UPDATE o AFTER DELETE come questo:
Tutte le azioni referenziali a catena causate direttamente dall'istruzione DELETE o UPDATE originale vengono eseguite per prime.
Se sono presenti trigger AFTER definiti nelle tabelle modificate, tali trigger vengono attivati dopo che sono state eseguite tutte le azioni a cascata. Questi trigger vengono eseguiti nell'ordine inverso delle azioni a cascata. Se vengono definiti più trigger per la stessa tabella, vengono attivati in ordine casuale, a meno che non venga specificato un trigger per la prima e l'ultima tabella dedicati. Questo ordine è determinato dalla procedura.
Se le sequenze di azioni a catena hanno origine da una tabella che era l'obiettivo immediato di un'azione DELETE o UPDATE, l'ordine in cui i trigger vengono attivati da tali sequenze di azioni non è definito. Tuttavia, una sequenza di azioni attiva sempre tutti i suoi trigger prima della successiva.
Un trigger AFTER su una tabella che è l'obiettivo immediato di un'azione DELETE o UPDATE si attiva indipendentemente dal fatto che siano state modificate righe. In questo caso, nessun'altra tabella è interessata dalla cascata.
Se uno dei trigger precedenti esegue operazioni DELETE o UPDATE su altre tabelle, tali operazioni possono attivare le proprie sequenze di azioni a cascata. Questi flussi di lavoro secondari vengono elaborati per ogni operazione DELETE o UPDATE dopo che tutti i trigger dei flussi di lavoro primari sono stati eseguiti. Questo processo può essere ripetuto in modo ricorsivo per successive operazioni DELETE o UPDATE.
L'esecuzione di CREATE, ALTER, DELETE o altre operazioni DDL all'interno dei trigger può causare l'attivazione dei trigger DDL. Ciò potrebbe comportare ulteriori operazioni DELETE o UPDATE, che avvieranno ulteriori sequenze a cascata e attiveranno i propri trigger.
Se si verifica un errore in una particolare sequenza di azioni di riferimento a cascata, nessun trigger AFTER verrà attivato in tale sequenza e verrà eseguito il rollback delle operazioni DELETE o UPDATE generate da tale sequenza.
Una tabella in cui è definito un trigger INSTEAD OF può anche avere una clausola REFERENCES che specifica una particolare azione a cascata. Tuttavia, un trigger AFTER sulla tabella di destinazione dell'azione a cascata può eseguire un'istruzione INSERT, UPDATE o DELETE su un'altra tabella o vista, che attiverà un trigger INSTEAD OF su quell'oggetto.
La tabella seguente elenca le attività comuni relative ai vincoli di chiave primaria e di chiave esterna.
È così che ci siamo avvicinati impercettibilmente a un argomento molto importante: chiavi primarie ed esterne. Se i primi sono usati da quasi tutti, i secondi vengono ignorati per qualche motivo. Ma invano. Le chiavi esterne non sono un problema, sono un vero aiuto nell'integrità dei dati.
Abbiamo già parlato molto di campi chiave, ma non li abbiamo mai utilizzati. La cosa più interessante è che tutto ha funzionato. Questo è un vantaggio, o forse uno svantaggio della base Dati Microsoft SQL Server e MS Access. Nelle tabelle Paradox, questo trucco non funzionerà e senza la presenza di un campo chiave, la tabella sarà di sola lettura.
In una certa misura, le chiavi sono vincoli e potrebbero essere considerate insieme all'istruzione CHECK, poiché la dichiarazione avviene in modo simile e viene utilizzata anche l'istruzione CONSTRAINT. Diamo un'occhiata a questo processo con un esempio. Per fare ciò, creeremo una tabella di due campi "guid" e "vcName". In questo caso, il campo "guid" è impostato come chiave primaria:
CREATE TABLE Globally_Unique_Data (guid uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (Guid))
La cosa più gustosa qui è la linea CONSTRAINT. Come sappiamo, dopo parola chiave viene il nome del vincolo e la chiave di dichiarazione non fa eccezione. Per nominare una chiave primaria, consiglio di utilizzare PK_name, dove name è il nome del campo che dovrebbe diventare la chiave principale. L'abbreviazione PK deriva da Primary Key (chiave primaria).
Dopodiché, invece della parola chiave CHECK, che abbiamo usato nei vincoli, c'è un'istruzione PRIMARY KEY, che indica che non abbiamo bisogno di un controllo, ma di una chiave primaria. Le parentesi indicano uno o più campi che comporranno la chiave.
Ricorda che due righe non possono avere lo stesso valore in un campo chiave, in questo il vincolo di chiave primaria è identico al vincolo univoco. Ciò significa che se si imposta il campo per la memorizzazione del cognome come chiave primaria, non sarà possibile scrivere due Ivanov con nomi diversi in tale tabella. Ciò viola il vincolo della chiave primaria. Questo è il motivo per cui le chiavi sono vincoli e sono dichiarate come il vincolo CHECK. Ma questo non vale solo per le chiavi primarie e le chiavi secondarie con unicità.
IN questo esempio, la chiave primaria è un campo di tipo uniqueidentifier (GUID). Il valore predefinito per questo campo è il risultato dell'esecuzione della procedura del server NEWID.
Attenzione
È possibile creare una sola chiave primaria per una tabella
Per semplicità di esempio, è preferibile utilizzare come chiave un tipo numerico e, se il database lo consente, sarà preferibile che sia del tipo "autoincremento" (numero che aumenta/diminuisce automaticamente). In MS SQL Server, tale campo è IDENTITY e in MS Access è un campo del tipo "contatore".
L'esempio seguente mostra come creare una tabella di prodotti con un campo intero a incremento automatico come chiave primaria:
CREATE TABLE Prodotti (id int IDENTITY(1, 1), product varchar(50), Price money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id))
È questo tipo di chiave che useremo più spesso, perché i numeri di facile lettura verranno memorizzati nel campo chiave ed è più facile e più visivo lavorare con essi.
La chiave primaria può contenere più di una colonna. L'esempio seguente crea una tabella in cui i campi "id" e "Prodotto" formano la chiave primaria, il che significa che verrà creato un indice univoco su entrambi i campi:
CREATE TABLE Products1 (id int IDENTITY(1, 1), Product varchar(50), Price money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id, [Product name]))
Molto spesso i programmatori creano un database con un campo chiave sotto forma di un numero intero, ma allo stesso tempo è chiaro nell'attività che determinati campi devono essere univoci. E perché non creare immediatamente una chiave primaria da quei campi che dovrebbero essere univoci e non sarà necessario creare soluzioni separate per questo problema.
L'unico aspetto negativo di una chiave primaria a più colonne è il problema della creazione di relazioni. Qui devi uscire con vari metodi, ma il problema è ancora risolvibile. Devi solo inserire un campo di tipo uniqueidentifier e stabilire una connessione su di esso. Sì, in questo caso otteniamo una chiave primaria univoca e un campo di tipo uniqueidentifier, ma questa ridondanza di conseguenza non sarà maggiore della stessa tabella in cui la chiave primaria è uniqueidentifier e i vincoli di unicità sono impostati sui campi che dovrebbero essere unico. Cosa scegliere? Dipende da compito specifico e qualunque cosa funzioni meglio per te.
Una chiave esterna è anche un vincolo CONSTRAINT e rappresenta una relazione tra due tabelle. Diciamo che hai due tabelle:
Una persona può avere diversi telefoni, quindi abbiamo separato l'archiviazione dei dati in diverse tabelle. La Figura 1.4 mostra visivamente la relazione tra le due tabelle. Se hai già lavorato con tabelle collegate, questo ti basterà. Se senti parlare di connessioni per la prima volta, proviamo a esaminare il problema più da vicino.
Ad esempio, prendiamo un tavolo di tre persone. La tabella 1.3 mostra il contenuto della tabella "Nomi". Ci sono solo tre linee e ognuna ha la sua chiave principale univoca. Per unicità, quando creiamo la tabella, renderemo la chiave un campo incrementato automaticamente.
Tabella 1.3 Contenuto della tabella Nomi
Tabella 1.4. Il contenuto della tabella Phones
La tabella 1.4 contiene cinque numeri di telefono. Il campo della chiave master ha anche una chiave master univoca, che può anche essere incrementata automaticamente. La chiave secondaria è una relazione con la chiave primaria della tabella Nomi. Come funziona questa connessione? Petrov ha come chiave principale nella tabella Nomi il numero 1. Nella tabella Telefoni, cerchiamo il numero 1 nella chiave secondaria e otteniamo i numeri di telefono di Petrov. Lo stesso vale per il resto delle voci. Visivamente, la connessione può essere vista nella Figura 1.5.
Tale memorizzazione dei dati è molto conveniente. Se non fosse possibile creare tabelle collegate, nella tabella Nomi dovremmo inserire tutti i numeri di telefono in un unico campo. Questo è scomodo in termini di utilizzo, supporto e recupero dei dati.
Puoi creare più campi Nomi in una tabella, ma la domanda è quanti. Una persona può avere solo 1 telefono e io, ad esempio, ne ho 3, senza contare i lavoratori. Un gran numero di campi porta alla ridondanza dei dati.
È possibile che ogni telefono nella tabella Nomi abbia una riga separata con il cognome, ma questo è facile solo per un esempio così semplice, quando devi inserire solo il cognome e puoi facilmente inserire diverse voci per Petrov con diversi numeri di telefono. E se ci sono 10 o 20 campi? Quindi, la creazione di due tabelle collegate da una chiave esterna può essere vista nel listato 1.6.
Listato 1.6. Creazione di tabelle collegate da una chiave esterna
CREATE TABLE Nomi (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Telefoni (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName) RIFERIMENTI Nomi (idName))
Leggi attentamente il contenuto dell'inserzione. È abbastanza interessante perché utilizza alcuni degli operatori che abbiamo già trattato e ulteriore esempio non impedire. Per entrambe le tabelle, viene creato un campo chiave che viene prima, è di tipo int e incrementa automaticamente da 1 in incrementi di uno. Il campo chiave viene reso la chiave master con un vincolo CONSTRAINT.
Nella descrizione della tabella Phones, l'ultima riga contiene una nuova dichiarazione per noi, ovvero una dichiarazione di chiave esterna utilizzando l'operatore FOREIGN KEY. Come puoi vedere, anche questa è una limitazione e capirai perché poco dopo. Le parentesi indicano il campo della tabella che deve essere collegato a un'altra tabella. Segue la parola chiave REFERENCES (reference), il nome della tabella con cui deve essere la relazione (Names) e tra parentesi il nome del campo ("idName"). Pertanto, abbiamo stabilito una connessione, mostrata nella Figura 1.4.
Attenzione!
Una chiave esterna può fare riferimento solo alla chiave primaria di un'altra tabella oa un vincolo univoco. Ciò significa che la parola chiave REFERENCES deve essere seguita dal nome della tabella e che solo la chiave primaria o un campo con un vincolo UNIQUE possono essere specificati tra parentesi. Non è possibile specificare altri campi.
Ora, se riesci a riempire le tabelle con i dati. I seguenti tre comandi aggiungono i tre cognomi che abbiamo visto nella Tabella 1.3:
INSERT INTO Nomi(vcName) VALUES("Petrov") INSERT INTO Nomi(vcName) VALUES("Ivanov") INSERT INTO Nomi(vcName) VALUES("Sidorov")
Se hai già lavorato con SQL, puoi aggiungere voci anche per la tabella telefonica. Ometterò questi comandi, ma puoi vederli nel file foreign_keys.sql nella directory Chapter1 sul CD.
Il nostro compito ora è vedere quali sono le azioni restrittive di una chiave esterna, scopriamolo. Abbiamo indicato una relazione esplicita tra due campi in tabelle diverse. Se provi ad aggiungere alla tabella telefonica un record con un identificatore nel campo "idName" che non esiste nel campo omonimo (il nome poteva essere reso diverso) nella tabella con i cognomi, si verificherà un errore . Ciò interromperà la relazione tra le due tabelle e il vincolo di chiave esterna impedirà l'esistenza di record senza una relazione.
La restrizione si applica anche in caso di modifica o eliminazione di record. Ad esempio, se provi a eliminare la riga con il cognome Petrov, otterrai un errore di vincolo di chiave esterna. Non è possibile eliminare record con righe correlate esternamente. Innanzitutto, è necessario eliminare tutti i telefoni per questa voce e solo dopo sarà possibile eliminare la linea stessa con il nome Petrov.
Durante la creazione della chiave esterna, è possibile specificare ON DELETE CASCADE o ON UPDATE CASCADE. In questo caso, se elimini il record Petrov dalla tabella Nomi o modifichi l'identificatore, tutti i record nella tabella Telefoni associati alla riga Petrov verranno aggiornati automaticamente. Mai. No, devi scrivere lettere maiuscole R: MAI farlo. Tutto deve essere rimosso o modificato manualmente. Se l'utente elimina accidentalmente una voce dalla tabella Nomi, verranno eliminati anche i telefoni corrispondenti. Ha senso quindi creare una chiave esterna se metà del suo potere restrittivo scompare! Tutto deve essere fatto solo manualmente e non è consigliabile modificare gli identificatori.
La cancellazione delle tabelle stesse deve iniziare anche dalla tabella figlia, cioè da Phones, e solo allora si può cancellare la tabella principale Nomi.
Infine, ti mostrerò come abbinare magnificamente nomi e numeri di telefono da due tabelle:
SELECT vcName, vcPhone FROM Nomi, Telefoni WHERE Nomi.idName=Telefoni.idName
Parleremo di più di questi tipi di query nel Capitolo 2. Per ora, ho incluso un esempio solo per mostrarvi la potenza delle tabelle collegate.
Una tabella può contenere fino a 253 chiavi esterne, sufficienti anche per i database più complessi. Personalmente, ho dovuto lavorare con database in cui il numero di chiavi esterne non superava 7 per tabella. Se di più, molto probabilmente il database è progettato in modo errato, sebbene vi siano delle eccezioni.
La tabella stessa può anche avere un massimo di 253 chiavi esterne. Le chiavi esterne in una tabella sono meno comuni, per lo più non più di 3. Molto spesso, una tabella può avere molti collegamenti ad altre tabelle.
Una chiave esterna può fare riferimento alla stessa tabella in cui è stata creata. Ad esempio, hai una tabella delle posizioni in un'organizzazione, come mostrato nella tabella 1.5. La tabella è composta da tre campi: chiave primaria, chiave esterna e titolo di lavoro. Possono esserci molte posizioni in qualsiasi organizzazione, ma sarebbe abbastanza logico visualizzare i loro nomi e la struttura di subordinazione in un'unica tabella. Per fare ciò, la chiave esterna deve essere associata alla chiave primaria della tabella dei lavori.
Tabella 1.5. Tabella con link interno
Di conseguenza, otteniamo che il CEO ha una chiave esterna zero, ad es. questa posizione è a capo di tutte le altre. Per il direttore commerciale e il direttore degli affari generali la chiave estera punta alla fila del direttore generale. Ciò significa che queste due posizioni riportano direttamente al CEO. E così via.
Vediamo come possiamo creare tutto questo sotto forma di query SQL:
CREATE TABLE Positions (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERENCES Positions (idPosition))
Come puoi vedere, la chiave esterna si riferisce semplicemente alla stessa tabella che stiamo creando. Sul CD, nella directory Chapter1, è possibile vedere nel file foreign_keys_to_self.sql un esempio di creazione di questa tabella, riempimento con dati e visualizzazione delle posizioni, tenendo conto della loro subordinazione. Nel prossimo capitolo, esamineremo la possibilità di lavorare con tali tabelle in modo più dettagliato.
Finora abbiamo considerato la relazione classica, quando una riga della tabella dati principale corrisponde a una riga della tabella associata. Tale relazione è chiamata uno-a-molti. Ma ci sono altre relazioni, e ora ne considereremo un'altra: uno a uno, quando un record nella tabella principale è correlato a un record in un altro. Per implementare ciò, è sufficiente collegare le chiavi primarie di entrambe le tabelle. Poiché le chiavi primarie non possono essere ripetute, solo una riga può essere correlata in entrambe le tabelle.
L'esempio seguente crea due tabelle che hanno una relazione tra chiavi primarie:
CREATE TABLE Nomi (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Telefoni (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), VINCOLO FK_idPhone FOREIGN KEY (idPhone) RIFERIMENTI Nomi (idName))
Solo una delle tabelle necessita di una chiave esterna. Poiché la relazione è uno a uno, non importa in quale tabella crearla.
La relazione più complessa è quella molti-a-molti, in cui molti record di una tabella corrispondono a molti record di un'altra tabella. Per implementare questo, due tabelle non sono sufficienti, sono necessarie tre tabelle.
Per prima cosa devi capire quando è possibile utilizzare una relazione molti-a-molti? Diciamo che hai due tabelle: un elenco di residenti e un elenco di numeri di telefono. Ci possono essere più numeri in un appartamento, il che significa che due telefoni possono appartenere allo stesso cognome. Quindi è una relazione uno-a-molti. D'altra parte, possono esserci due famiglie in un appartamento (appartamento comune o solo un inquilino che utilizza il telefono del proprietario), il che significa che anche il rapporto tra il telefono e il residente è uno a molti. E l'opzione più difficile è avere due telefoni in un appartamento comune. In questo caso, diversi residenti dell'appartamento utilizzano entrambi i numeri. Quindi risulta che "molte" famiglie possono usare "molti" telefoni (comunicazione molti-a-molti).
Come implementare una relazione molti-a-molti? A prima vista, questo è impossibile nel modello relazionale. 10 anni fa stavo cercando diverse varianti e di conseguenza ha appena creato una tabella che traboccava di ridondanza di dati. Ma un giorno ho ricevuto un compito, grazie al quale è già emersa un'ottima soluzione dalla condizione: è necessario creare due tabelle di residenti di appartamenti e telefoni e implementare solo la chiave primaria in esse. Le chiavi esterne non sono necessarie in questa tabella. E qui la comunicazione tra i tavoli deve avvenire attraverso il terzo tavolo di collegamento. A prima vista, questo è difficile e poco chiaro, ma una volta compreso questo metodo, vedrai tutta la potenza di questa soluzione.
Le tabelle 1.6 e 1.7 mostrano rispettivamente esempi di cognomi e tabelle telefoniche. E la tabella 1.8 mostra una tabella dei collegamenti.
Tabella 1.6. Tabella dei cognomi
Tabella 1.7. Tavolo del telefono
Tabella 1.8. Tavolo del telefono
Vediamo ora quale sarà la logica di ricerca dei dati in una relazione molti-a-molti. Diciamo che dobbiamo trovare tutti i telefoni che appartengono a Ivanov. Ivanov ha una chiave primaria uguale a 1. Troviamo nella tabella di collegamento tutti i record in cui il campo "Relazione con nome" è uguale a 1. Questi saranno i record 1 e 2. In questi record si trovano gli identificatori 1 e 2 nel campo "Rapporto con il telefono", rispettivamente, e quindi Ivanov possiede i numeri della tabella del telefono, che si trovano nelle righe 1 e 2.
Ora risolviamo il problema inverso: determiniamo chi ha accesso al numero di telefono 567575677. Questo numero nella tabella del telefono ha una chiave di 3. Stiamo cercando tutti i record nella tabella dei collegamenti, dove il campo "Connessione con il telefono" è uguale a 3. Si tratta di record con i numeri 4 e 5, che nel campo "Relazione con il nome" contengono rispettivamente i valori 2 e 3. Se ora guardi la tabella dei cognomi, vedrai Petrov e Sidorov sotto i numeri 2 e 3. Quindi, sono questi due residenti che usano il numero di telefono 567575677.
Esamina tutte e tre le tabelle e assicurati di capire quali numeri di telefono appartengono a quali residenti e viceversa. Se vedi questa connessione, capirai che è semplice come tre centesimi e puoi implementarla rapidamente nei tuoi progetti.
CREATE TABLE Nomi (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Telefoni (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)) CREATE TABLE LinkTable (idLinkTable uniqueidentifier DEFAULT NEWID(), idName uniqueidentifier, idPhone uniqueidentifier, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES Phones (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName) REFERENCES Nomi (idName) )
La tabella di giunzione ha due chiavi esterne che si collegano alle tabelle dei nomi e dei telefoni e una chiave primaria che rende i record univoci.
Ho scelto il campo GUID come chiave primaria, perché è più conveniente per risolvere questo particolare compito. Il fatto è che dobbiamo inserire i record in due tabelle e in entrambi i casi dobbiamo specificare la stessa chiave. Il valore GUID può essere generato e quindi utilizzato quando si inseriscono dati in entrambe le tabelle.
Puoi anche usare un campo auto-incrementante come chiave, ma in questo caso il problema è un po' più difficile da risolvere, più precisamente, è scomodo risolvere il problema. Ad esempio, quando si aggiunge un numero di telefono, è necessario prima inserire la riga corrispondente nella tabella, quindi trovarla, determinare la chiave assegnata alla riga e quindi stabilire una connessione.
In questa fase, siamo limitati alla sola creazione di tabelle e nella sezione 2.8 torneremo su questo argomento e impareremo e impareremo come lavorare con le tabelle correlate. L'utilizzo delle relazioni uno-a-uno e uno-a-molti non è molto diverso, poiché in questo schema sono coinvolte solo due tabelle. La relazione molti-a-molti è un po' più complicata a causa della tabella di join, quindi la tratteremo separatamente nella Sezione 2.27.
La Figura mostra una tabella (rapporto di grado 5) contenente alcune informazioni sui dipendenti di una ipotetica impresa. Le righe della tabella corrispondono alle tuple. Ogni riga è in realtà una descrizione di un oggetto del mondo reale (in questo caso, un dipendente), le cui caratteristiche sono contenute nelle colonne. Le relazioni relazionali corrispondono agli insiemi di entità, mentre le tuple corrispondono alle entità. Vengono chiamate le colonne in una tabella che rappresenta una relazione relazionale attributi.
Ogni attributo è definito su un dominio, quindi il dominio può essere pensato come l'insieme di valori validi per quell'attributo. Diversi attributi della stessa relazione, e persino attributi di relazioni diverse, possono essere definiti sullo stesso dominio.
Viene chiamato un attributo il cui valore identifica in modo univoco le tuple chiave (o semplicemente chiave). La chiave è l'attributo "Numero del personale", poiché il suo valore è unico per ciascun dipendente dell'impresa. Se le tuple vengono identificate solo concatenando i valori di più attributi, si dice che la relazione ha una chiave composta.
chiave primaria- in un modello dati relazionale, una delle potenziali chiavi della relazione, scelta come chiave principale (o chiave di default).
Una relazione può contenere più chiavi. Viene sempre dichiarata una delle chiavi primario, i suoi valori non possono essere aggiornati. Vengono chiamate tutte le altre chiavi di relazione possibili chiavi.
Dal punto di vista della teoria, tutte le chiavi potenziali (possibili) della relazione sono equivalenti, cioè hanno le stesse proprietà di unicità e minimalità. Tuttavia, come principale, di solito viene scelta una delle potenziali chiavi, che è più conveniente per determinati scopi pratici, ad esempio per creare esterno keys in altri modi o per creare un indice cluster. Pertanto, di norma, viene scelta come chiave primaria quella che ha la dimensione più piccola ( archiviazione fisica) e/o include il minor numero di attributi.
Se chiave primariaè costituito da un singolo attributo, si chiama chiave semplice.
Se chiave primaria consiste di due o più attributi, si chiama chiave composita. Quindi, nome, cognome, patronimico, numero di passaporto, serie di passaporti non possono essere chiavi primarie separatamente, poiché possono essere le stesse per due o più persone. Ma non esistono due documenti personali dello stesso tipo con la stessa serie e numero. Pertanto, in una relazione contenente dati relativi a persone, la chiave primaria può essere un sottoinsieme di attributi, costituito dal tipo di documento personale, dalla sua serie e dal numero.
A differenza dei modelli di dati gerarchici e di rete, quello relazionale non ha il concetto di relazione di gruppo. Per riflettere le associazioni tra tuple di relazioni diverse, viene utilizzata la duplicazione delle loro chiavi.
Vengono chiamati gli attributi che sono copie delle chiavi di altre relazioni chiavi esterne.
Ad esempio, la relazione tra DEPARTMENT e EMPLOYEE viene creata copiando la chiave primaria "Numero_dipartimento" dalla prima relazione alla seconda. Pertanto, per ottenere l'elenco dei dipendenti di una determinata suddivisione, è necessario: 1) Dalla tabella REPARTO impostare il valore dell'attributo "Numero_dipartimento" Il corrispondente al dato "Department_Name". 2) selezionare tutti i record dalla tabella IMPIEGATO, valore dell'attributo "Numero_dipartimento" che è uguale a quello ottenuto nel passaggio precedente. Per scoprire in quale reparto lavora un dipendente, è necessario eseguire l'operazione inversa: 1) Determinare "Numero_dipartimento" dalla tabella IMPIEGATO. 2) In base al valore ottenuto troviamo una voce nella tabella REPARTO.
18. Normalizzazione nei database relazionali, il concetto di forma normale nella progettazione dei database.
forma normale - una proprietà di una relazione in un modello di dati relazionale che la caratterizza in termini di ridondanza, che può potenzialmente portare a risultati logicamente errati di campionamento o modifica dei dati. La forma normale è definita come l'insieme dei requisiti che una relazione deve soddisfare.
Viene chiamato il processo di conversione di un database in un formato normale normalizzazione . La normalizzazione ha lo scopo di portare la struttura del database in una forma che fornisca una ridondanza minima, ovvero la normalizzazione non ha lo scopo di ridurre o aumentare le prestazioni o ridurre o aumentare le dimensioni del database. L'obiettivo finale della normalizzazione è ridurre la potenziale incoerenza delle informazioni memorizzate nel database.
La ridondanza viene solitamente eliminata scomponendo le relazioni in modo tale che in ciascuna relazione vengano memorizzati solo fatti primari (ovvero fatti che non derivano da altri fatti memorizzati).
dipendenze funzionali.
Un database relazionale contiene sia informazioni strutturali che semantiche. La struttura di un database è determinata dal numero e dal tipo di relazioni incluse in esso e dalle relazioni uno-a-molti che esistono tra le tuple di tali relazioni. La parte semantica descrive l'insieme delle dipendenze funzionali che esistono tra gli attributi di queste relazioni. Diamo una definizione di dipendenza funzionale.
19. 1NF: definizioni di base e regole di trasformazione.
Per discutere la prima forma normale, è necessario dare due definizioni:
attributo semplice - un attributo i cui valori sono atomici (indivisibili).
Attributo complesso - si ottiene combinando più attributi atomici che possono essere definiti su domini uguali o diversi (è anche chiamato vettore o aggregato di dati).
Definizione della prima forma normale:
una relazione è in 1NF se i valori di tutti i suoi attributi sono atomici. . Altrimenti, non è affatto una tabella e tali attributi devono essere scomposti.
Considera un esempio:
Il database delle risorse umane dell'azienda deve archiviare informazioni sui dipendenti che puoi provare a rappresentare in relazione a
DIPENDENTE(NUMERO_DIPENDENTE, NOME, DATA_DI NASCITA, CRONOLOGIA_LAVORO, BAMBINI).
Da un attento esame di questa relazione, ne consegue che gli attributi "storia del lavoro" E "bambini" sono complessi, inoltre, gli attributi "storia del lavoro" include un altro attributo complesso "storia_stipendio".
Queste unità hanno questo aspetto:
CRONOLOGIA_LAVORO (DATA_RICEVIMENTO, NOME, CRONOLOGIA_STIPENDIO),
CRONOLOGIA_STIPENDIO (DATA_APPUNTAMENTO, STIPENDIO),
BAMBINI (CHILD_NAME, NASCITA_ANNO).
La loro relazione è mostrata in Fig. 3.3.
Fig.3.3. Relazione iniziale.
Per riportare la relazione originaria EMPLOYEE alla prima forma normale, è necessario scomporla in quattro relazioni, come mostrato nella figura seguente: 
Fig.3.4. Insieme normalizzato di relazioni.
Qui, la chiave primaria di ogni relazione è evidenziata in blu ei nomi delle chiavi esterne sono in blu. Ricordiamo che sono le chiavi esterne che servono a rappresentare le dipendenze funzionali che esistono nella relazione originale. Queste dipendenze funzionali sono indicate da linee con frecce.
L'algoritmo di normalizzazione è descritto da EF Codd come segue:
20. 2NF: definizioni di base e regole di trasformazione.
Molto spesso, la chiave primaria di una relazione include diversi attributi (nel qual caso si chiama composito) - si veda, ad esempio, la relazione BAMBINI mostrata in fig. 3.4 domanda 19. Questo introduce il concetto piena dipendenza funzionale.
Definizione:
Un attributo non chiave è funzionalmente dipendente da una chiave composta se è funzionalmente dipendente dalla chiave nel suo insieme, ma non è funzionalmente dipendente da nessuno dei suoi attributi costituenti.
Esempio:
Sia una relazione OFFERTA (N_FORNITORE, PRODOTTO, PREZZO).
Un fornitore può fornire merci diverse e le stesse merci possono essere fornite da fornitori diversi. Quindi la chiave della relazione è "N_fornitore + articolo". Consentire a tutti i fornitori di fornire beni allo stesso prezzo. Allora abbiamo le seguenti dipendenze funzionali:
La dipendenza funzionale incompleta dell'attributo "prezzo" dalla chiave porta alla seguente anomalia: quando il prezzo di un prodotto cambia, è necessaria una visione completa della relazione per modificare tutti i record sui suoi fornitori. Questa anomalia è una conseguenza del fatto che due fatti semantici sono combinati in una struttura dati. La seguente espansione fornisce la relazione in 2NF:
Quindi, si può dare
Definizione di seconda forma normale: Una relazione è in 2NF se è in 1NF e ogni attributo non chiave è funzionalmente dipendente dalla chiave.
21. 3NF: definizioni di base e regole di trasformazione.
Prima di discutere la terza forma normale, è necessario introdurre il concetto: dipendenza funzionale transitiva.
Definizione:
Siano X, Y, Z tre attributi di qualche relazione. In questo caso, X --> Y e Y --> Z, ma non c'è corrispondenza inversa, cioè Z -/-> Y e Y -/-> X. Allora Z dipende transitivamente da X.
Lascia che ci sia una relazione STORAGE ( DITTA, MAGAZZINO, VOLUME), che contiene informazioni sulle imprese che ricevono merci dai magazzini e sui volumi di questi magazzini. Attributo chiave - "ditta". Se ogni azienda può ricevere merci da un solo magazzino, allora a questo proposito ci sono le seguenti dipendenze funzionali:
Questo crea anomalie:
Per eliminare queste anomalie è necessario scomporre in due la relazione originaria:
Definizione di terza forma normale:
Una relazione è in 3NF se è in 2NF e ogni attributo non chiave non dipende transitivamente dalla chiave primaria.
In precedenza in questo libro, abbiamo evidenziato alcune relazioni che esistono tra determinati campi delle tabelle di esempio. Il campo snum nella tabella Clienti, ad esempio, corrisponde al campo snum nella tabella Venditori e nella tabella Ordini. Il campo cnum della tabella Clienti corrisponde anche al campo cnum della tabella Ordini. Abbiamo chiamato questo tipo di relazione integrità referenziale; e durante la discussione, hai visto come può essere utilizzato.
In questo capitolo, esplorerai l'integrità referenziale in modo più dettagliato e scoprirai tutto sui limiti che puoi utilizzare per supportarla. Vedrai anche come si applica questa limitazione quando usi i comandi di modifica DML. Poiché l'integrità referenziale implica il collegamento di campi o gruppi di campi, spesso tra tabelle, questa azione può essere un po' più complessa rispetto ad altri vincoli. Per questo motivo è bene conoscerlo a fondo, anche se non hai intenzione di creare tabelle. I tuoi comandi di modifica possono essere resi più efficienti con un vincolo di integrità referenziale (come con altri vincoli, ma un vincolo di integrità referenziale può influenzare tabelle diverse da quella in cui è definito) e alcune funzioni di query, come i join, sono ripetutamente strutturate in termini di relazioni di integrità referenziale (come evidenziato nel capitolo 8).
Quando un campo in una tabella fa riferimento a un altro, viene chiamato chiave esterna; e il campo a cui si riferisce è chiamato la chiave genitore. Quindi il campo snum nella tabella Clienti è la chiave esterna e il campo snum a cui fa riferimento nella tabella Venditori è la chiave padre.
Allo stesso modo, i campi cnum e snum della tabella Orders sono chiavi esterne che fanno riferimento alle rispettive chiavi padre denominate nella tabella Customers e nella tabella Salesmen. I nomi della chiave esterna e della chiave genitore non devono essere gli stessi, è solo una convenzione che seguiamo per rendere il join più comprensibile.
È chiaro che ogni valore nella chiave esterna deve essere rappresentato una volta, e solo una volta, nella chiave genitore.
Infatti, un dato valore di chiave esterna può fare riferimento a un solo valore di chiave genitore senza presumere che sia possibile il contrario: ovvero qualsiasi numero di chiavi esterne può fare riferimento a un singolo valore di chiave genitore. Puoi vederlo nelle tabelle di esempio dei nostri esempi. Sia Hoffman che Clemens sono assegnati a Peel, quindi entrambi i loro valori di chiave esterna corrispondono alla stessa chiave genitore, il che è fantastico. Un valore di chiave esterna deve fare riferimento a un solo valore di chiave padre, ma un valore di chiave padre può fare riferimento a qualsiasi numero di valori di chiave esterna. A titolo illustrativo, nella Figura 19.1 sono mostrati i valori della chiave esterna della tabella Clienti che corrispondevano alla loro chiave madre nella tabella Venditori. Per comodità, non abbiamo preso in considerazione il genere non correlato a questo esempio.
Come la maggior parte dei vincoli, può essere un vincolo di tabella o di colonna, sotto forma di una tabella che consente di utilizzare più campi come una singola chiave esterna.
* Devono avere lo stesso numero di colonne.
* In questa sequenza, la prima, la seconda, la terza, ecc., colonne dell'elenco di colonne di chiavi esterne devono avere gli stessi tipi di dati e le stesse dimensioni della prima, seconda, terza, ecc. chiave genitore. Le colonne negli elenchi di entrambe le colonne non devono avere lo stesso nome, anche se abbiamo utilizzato questo metodo nei nostri esempi per rendere più chiara la relazione.
Creiamo una tabella Clienti con il campo snum definito come chiave esterna riferita alla tabella Venditori: CREATE TABLE Clienti (cnum integer NOT NULL PRIMARY KEY cname char(10), city char(10), snum integer, FOREIGN KEY (snum) RIFERIMENTI Venditori (snum ); Tenere presente che quando si utilizza ALTER TABLE invece di CREATE TABLE, per applicare un vincolo FOREIGN KEY, i valori specificati nella chiave esterna e nella chiave genitore devono trovarsi in uno stato di integrità referenziale. il comando verrà rifiutato.Sebbene ALTER TABLE sia molto utile da - per sua comodità, dovrai prima formare principi strutturali nel tuo sistema, quando possibile, come l'integrità referenziale.
Dal punto di vista dell'integrità del database, gli interrupt interni (o le eccezioni) sono ovviamente indesiderabili. Se li consenti e allo stesso tempo vuoi mantenere l'integrità del tuo database, puoi dichiarare i campi snum e cnum nella tabella Orders come chiavi esterne indipendenti di quei campi rispettivamente nella tabella Sales e nella tabella Customer. Infatti, l'utilizzo del campo snum nella tabella Orders, come abbiamo fatto noi, è facoltativo, sebbene fosse utile farlo per varietà. Il campo cnum che collega ogni ordine di clienti nella tabella Clienti, nella tabella Ordini e nella tabella Clienti, deve essere sempre condiviso per poter trovare il campo snum corretto per quell'ordine (senza eccezioni). Ciò significa che scriviamo un'informazione (quale cliente è assegnato a quale fornitore) due volte e sarà necessario lavorare di più per garantire che entrambe le versioni siano coerenti. Se non abbiamo un vincolo di chiave esterna come menzionato sopra, questa situazione sarà particolarmente problematica perché ogni ordine dovrà essere controllato manualmente (insieme alla query) per assicurarsi che il venditore corretto abbia accreditato ogni rispettiva vendita. Avere questo tipo di ridondanza delle informazioni nel database è chiamato denormalizzazione, che è indesiderabile in un database relazionale ideale, sebbene in pratica possa essere consentito. La demoralizzazione può rendere alcune query più veloci, poiché una query su una singola tabella è sempre significativamente più veloce di una query su un join.
Poiché è coinvolta la questione della modifica dei valori della chiave principale, la risposta, per definizione ANSI, è ancora più semplice, ma forse un po' più limitata: qualsiasi valore di una chiave principale a cui fa riferimento un valore di chiave esterna non può essere eliminato o modificato. Ciò significa, ad esempio, che non è possibile rimuovere un cliente dalla tabella Clienti mentre ha ancora ordini nella tabella Ordini. A seconda di come si utilizzano queste tabelle, ciò può essere desiderabile o problematico. Questo però è sicuramente meglio che avere un sistema che permette di togliere un cliente con ordini in corso e uscire dalla tabella Ordini riferendosi a clienti inesistenti. Il punto di questo sistema di vincoli è che il creatore della tabella Ordini, utilizzando la tabella Clienti e la tabella Venditori come chiavi padre, può imporre restrizioni significative sulle azioni in queste tabelle. Per questo motivo, non potrai utilizzare una tabella che non controlli (ovvero, non l'hai creata e non ne sei il proprietario) a meno che il proprietario (creatore) di quella tabella non ti conceda espressamente il diritto di farlo (come spiegato nel capitolo 22). Ce ne sono altri azioni possibili modifiche alla chiave madre che non fanno parte di ANSI ma possono essere trovate in alcuni programmi commerciali. Se desideri modificare o eliminare il valore di riferimento della chiave genitore corrente, ci sono essenzialmente tre possibilità:
Anche all'interno di queste tre categorie, potresti non voler elaborare tutti i comandi di modifica in questo modo. INSERT, ovviamente, è irrilevante. Mette i nuovi valori della chiave genitore nella tabella in modo che nessuno di quei valori possa essere chiamato al momento. Tuttavia, potresti voler consentire le modifiche a cascata senza eliminazioni e viceversa. La situazione migliore potrebbe essere quella che consente di definire una qualsiasi delle tre categorie, indipendentemente dai comandi UPDATE e DELETE. Faremo quindi riferimento agli effetti di aggiornamento e di eliminazione, che determinano cosa accade se si emette un comando UPDATE o DELETE sulla chiave genitore. Questi effetti di cui abbiamo parlato sono chiamati modifiche RESTRICTED, modifiche CASCADES e modifiche NULL. Le effettive capacità del tuo sistema dovrebbero rientrare nel rigoroso standard ANSI - effetti di modifica ed eliminazione, entrambi limitati automaticamente - per la situazione più ideale descritta sopra. A titolo illustrativo, mostreremo alcuni esempi di ciò che è possibile fare con l'intera gamma di effetti di modifica ed eliminazione. Ovviamente, gli effetti di modifica ed eliminazione, che sono funzionalità non standard, mancano della sintassi di stato standard. La sintassi che usiamo qui è semplice da scrivere e servirà per illustrare la funzione di questi effetti.
Per completezza dell'esperimento, supponiamo di avere un motivo per modificare il campo snum della tabella Salesperson nel caso in cui la nostra tabella Salesperson modifichi le partizioni. (Di solito la modifica delle chiavi primarie non è qualcosa che raccomandiamo di fare in pratica. È solo un altro motivo per cui le chiavi primarie esistenti non fanno altro che agire come chiavi primarie: non dovrebbero cambiare.) Quando cambi il numero del fornitore, vuoi mantenere tutto dei suoi clienti. Tuttavia, se questo venditore lascia la sua ditta o azienda, potresti non voler rimuovere i suoi clienti quando lo rimuovi dal database. Invece, ti consigliamo di assicurarti che i clienti siano assegnati a qualcun altro. Per fare ciò, è necessario specificare UPDATE con un effetto Cascading e DELETE con un effetto Limited. CREATE TABLE Clienti (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer RIFERIMENTI Venditori, AGGIORNAMENTO Venditori CASCADES, DELETE Venditori RESTRICTED); Se ora provi a rimuovere Peel dalla tabella Fornitori, il comando non sarà valido fino a quando non cambierai il valore di sesso snum dei clienti Hoffman e Clemens in un altro fornitore assegnato. D'altra parte, puoi cambiare il campo snum di Peel a 1009 e anche Hoffman e Clemens verranno automaticamente modificati.
Il terzo effetto sono le modifiche NULL. Succede che quando i venditori lasciano l'azienda, i loro ordini correnti non vengono trasferiti a un altro venditore. D'altra parte, vuoi annullare automaticamente tutti gli ordini per i clienti di cui elimini gli account. Modificando i numeri del venditore o del cliente, puoi semplicemente trasferirli a lui. L'esempio seguente mostra come creare una tabella Order utilizzando questi effetti. CREATE TABLE Orders (onum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL cnum integer NOT NULL REFERENCES Clienti snum integer REFERENCES Venditori, AGGIORNAMENTO CASCATE Clienti, CANCELLAZIONE CASCATE Clienti, AGGIORNAMENTO CASCATE Venditori, CANCELLA Venditori NULL); Ovviamente, in un comando DELETE con un effetto di modifica NULL sulla tabella Salespersons, il vincolo NOT NULL deve essere rimosso dal campo snum.
2. Complichiamo il problema. Ridefinire la tabella Ordini come segue: aggiungere una nuova colonna denominata prev da identificare per ogni ordine, il campo onum dell'ordine precedente per questo cliente attuale. Fallo usando una chiave esterna che fa riferimento alla tabella degli ordini stessa. La chiave esterna deve anche fare riferimento al campo cnum del cliente, che fornisce una relazione prescritta tra l'ordine corrente e quello di riferimento.
(Vedi Appendice A per le risposte.)
Se sei come l'autore di questo corso in quanto ti piace cercare risposte a una domanda che ti interessa in modo complesso, in opere diverse di autori diversi, allora non puoi fare a meno di notare una certa confusione nelle definizioni principale (maestro) -> subordinato (particolare) tabelle. Ricordiamo che la tabella principale viene spesso definita tabella padre e la tabella subordinata come tabella figlio.
Ciò è probabilmente dovuto al modo in cui queste definizioni vengono interpretate nel DBMS locale e SQL-server.
Nel DBMS locale, la tabella principale è quella che contiene i dati principali e la tabella subordinata è quella aggiuntiva. Prendi, ad esempio, tre tabelle correlate. Il primo contiene dati sulle vendite, il secondo contiene dati sui prodotti e il terzo contiene dati sui clienti:
Riso. 18.1.
Qui, le informazioni principali sono memorizzate nella tabella delle vendite, quindi è la principale (padre). Ulteriori informazioni sono memorizzate nelle tabelle di prodotti e clienti, il che significa che sono bambini. Questo è comprensibile: una figlia non può avere due madri biologiche, ma una madre è perfettamente in grado di dare alla luce due figlie.
Ma i server di database SQL hanno una diversa definizione delle relazioni: quando un campo in una tabella fa riferimento a un campo in un'altra tabella, viene chiamato chiave esterna. E il campo a cui si riferisce è chiamato genitore o chiave primaria. Una tabella che ha una chiave esterna (un collegamento a un record in un'altra tabella) è spesso chiamata tabella figlio e una tabella con chiave genitore- genitore. Anche nella definizione delle relazioni, dicono che un genitore può avere un solo record univoco, a cui possono fare riferimento diversi record. tavolo bambino.
Quindi, nell'esempio precedente, la tabella delle vendite ha due chiavi esterne: id prodotto e id cliente. Ed entrambi i tavoli sul lato destro della figura hanno chiave genitore"Identificatore". Poiché un cliente o un prodotto può apparire più di una volta nella tabella delle vendite, risulta che entrambe le tabelle sul lato destro della figura sono genitori e la tabella a sinistra è un bambino. Dal momento che ora stiamo studiando InterBase-SQL server di database, ci faremo guidare da queste definizioni nelle lezioni successive. Per non confondere ulteriormente questa confusione, concorderemo immediatamente: tavolo bambino ha una chiave esterna ( FOREIGN KEY ) in un'altra tabella.
CHIAVE PRIMARIA- chiave primaria, è uno dei principali tipi di vincoli nel database. La chiave primaria ha lo scopo di identificare in modo univoco un record in una tabella e deve essere univoca. Le chiavi primarie PRIMARY KEY sono in tabelle che sono chiamate genitore (Parent). Non confondere la chiave primaria con gli indici primari dei database locali, la chiave primaria non è un indice, ma un vincolo. Quando si crea una chiave primaria InterBase crea automaticamente per lui indice univoco. Tuttavia, se creiamo indice univoco, questo non creerà vincoli di chiave primaria. Una tabella può avere solo una PRIMARY KEY .
Supponiamo di avere una tabella con un elenco di dipendenti. Il campo "Cognome" può contenere gli stessi valori (nomi con lo stesso nome), quindi non può essere utilizzato come chiave primaria. Raramente, ma ci sono omonimi che, inoltre, hanno gli stessi nomi. Ancora più raro, ma ci sono omonimi completi, quindi anche tutti e tre i campi "Cognome" + "Nome" + "Patronimico" non possono garantire l'univocità del record e non possono essere la chiave primaria. In questo caso, la via d'uscita, come prima, è aggiungere un campo - identificatore, che contiene il numero di serie di questa persona. Tali campi sono solitamente auto-incrementati (parleremo dell'organizzazione dei campi auto-incrementali nelle prossime lezioni). COSÌ,
chiave primaria - questo è uno o più campi nella tabella, la cui combinazione è unica per ogni record.
Se la chiave primaria ha una singola colonna (come spesso accade), viene utilizzato l'identificatore PRIMARY KEY quando definizione di colonna:
CREATE TABLE Prim_1(Stolbec1 INT NOT NULL CHIAVE PRIMARIA, Stolbec2 VARCHAR(50))
Se la chiave primaria è costruita su più colonne, lo specificatore viene posizionato dopo che tutti i campi sono stati definiti:
CREATE TABLE Prim_2(Stolbec1 INT NOT NULL, Stolbec2 VARCHAR(50) NOT NULL, PRIMARY KEY (Stolbec1, Stolbec2))
Come puoi vedere dagli esempi, la chiave primaria deve necessariamente avere un vincolo di colonna NOT NULL.
UNICO- chiave univoca. L'identificatore UNIQUE specifica che tutti i valori dato campo deve essere univoco, quindi anche tali campi non possono contenere valori NULLO. Si può dire che la chiave UNIQUE è un'alternativa alla chiave primaria, ma ci sono delle differenze. La differenza principale è che deve esserci una sola chiave primaria, mentre possono esserci diverse chiavi univoche. Inoltre, non è possibile creare un vincolo UNIQUE sullo stesso set di colonne utilizzato per un vincolo PRIMARY KEY o un altro vincolo UNIQUE. Le chiavi univoche, come le chiavi primarie, si trovano nelle tabelle padre di altre tabelle.
Una colonna dichiarata con un vincolo UNIQUE, come una chiave primaria, può essere utilizzata per applicare l'integrità referenziale tra padre e tabelle figlio. Mentre la chiave esterna tavolo bambino farà riferimento a quei campi. Come nel caso di una chiave primaria, quando viene creata una chiave univoca, verrà creata automaticamente una chiave per essa. indice univoco. Ma non viceversa. Un esempio di creazione di una tabella con una chiave primaria e due chiavi univoche:
CREATE TABLE Prim_3(Stolbec1 INT NOT NULL PRIMARY KEY, Stolbec2 VARCHAR(50) NOT NULL UNIQUE, Stolbec3 FLOAT NOT NULL UNIQUE)
CHIAVE STRANIERA- chiave esterna. Questo è uno strumento molto potente per garantire l'integrità referenziale tra le tabelle, che consente non solo di monitorare la presenza di collegamenti corretti, ma anche di gestirli automaticamente. Le chiavi esterne sono contenute in tabelle figlie ( Child ) di altre tabelle. Integrità referenzialeè fornito appunto da una chiave esterna che fa riferimento all'or primario
