Tabelle. Chiavi primarie e generatori
NOTICE: Questo documento è il capitolo del libro “The InterBase World” scritto da Alexey Kovyazin e Serg Vostrikov.
InterBase è un DBMS relazionale. Inoltre, ciò significa che tutti i dati in InterBase sono memorizzati come tabelle. La tabella, come è realizzata dal punto di vista SQL, è molto simile alla tabella ordinaria che può essere disegnata a mano su un foglio di carta o creata in un programma come Microsoft Excel. Le tabelle in InterBase hanno colonne e righe dove vengono inseriti i dati. La tabella deve necessariamente avere un nome, unico all’interno di un database. Le tabelle sono il principale archivio di informazioni in un database e di conseguenza dovresti essere molto attento quando crei le tabelle.
Ci sono regole che descrivono come creare tabelle nel database relazionale, riflettendo i dati del mondo reale e allo stesso tempo permettendo di organizzare una memorizzazione efficace delle informazioni in un database. Il processo di applicazione di queste regole per progettare un database “corretto” è chiamato normalizzazione. Abbiamo volutamente messo tra virgolette la parola “corretto”, perché “database normalizzato” e “database ottimizzato” non sono sinonimi. Non devi conformarti alle regole di normalizzazione in modo univoco - applica sempre la correzione per la specifica di un dato problema.
La normalizzazione delle tabelle in un database è trattata in dettaglio nel libro [14. e quindi non cercheremo di abbracciare l’inabbracciabile e torneremo al nostro tema di discussione - alle tabelle di InterBase. Consideriamo la sintassi della frase DDL (DDL - Data Definition Language, per maggiori dettagli vedi il glossario) che permette di creare tabelle:
CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);
Qui table è un nome della tabella creata, - la descrizione delle colonne (a volte diremo - campi) della tabella creata. L’opzione table [EXTERNAL [FILE] “”] significa che verrà creata la cosiddetta tabella esterna che non è memorizzata in un file di database condiviso, ma in un file separato con un nome . Come puoi vedere, tutto è semplice - definiamo un nome di tabella e le colonne che contiene. Ora considereremo in dettaglio come definire le colonne. La sintassi per creare una colonna è descritta dalla seguente frase DDL:
= col { datatype | COMPUTED [BY] (< expr>)} | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[COLLATE collation]
Questa è una definizione piuttosto grande, tuttavia nella definizione di una colonna solo una piccola parte delle frasi date è obbligatoria. Ogni colonna nella tabella deve avere un nome, unico all’interno della tabella, così come un tipo di dati definito dalla frase datatype, o un’espressione per calcolare il valore di una colonna (per colonne calcolate), o il dominio (vedi sotto), definito domain. I tipi di dati sono stati considerati nel capitolo “Tipi di dati”; quindi puoi facilmente capire come si forma l’espressione SQL per creare una tabella.
Colleghiamoci al nostro database FIRSTBASE.gdb creato in precedenza nel capitolo “Crea un database”, e proveremo a lavorare con le tabelle nella pratica. Quando si tratta di creare, eliminare e aggiornare tabelle, qualsiasi strumento amministrativo di InterBase - tra quelli elencati nell’applicazione “Strumenti per amministratori e sviluppatori InterBase”, così come l’utilità standard isql.exe da un set di fornitura di qualsiasi clone di InterBase sarà adatto.
Ecco un esempio di una semplice tabella chiamata TABLE_EXAMPLE contenente 3 campi di vari tipi:
CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Questa tabella illustra il caso più frequente che si verifica nel processo di sviluppo di un database. Tuttavia, ci sono anche altri metodi per definire i campi. Ad esempio, possiamo impostare un tipo di campo usando i domini. Il dominio è un tipo definito dall’utente per la comodità di applicare certe combinazioni di parametri di tipo. Ad esempio, è possibile definire il dominio D_ID per specificare campi di identificatori. Dopo aver definito il dominio, possiamo usarlo per impostare il tipo di un campo:
CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Il campo ID avrà il tipo definito dal dominio D_ID. Quindi, avendo definito il tipo del campo nel dominio, i controlli e i vincoli richiesti, possiamo applicare questo dominio molte volte per creare campi della stessa funzione. Ad esempio, monetario senza noiosi e pericolosi errori di copia delle definizioni di tipi variabili. Il terzo modo per impostare una colonna nella tabella è definirla come calcolata (COMPUTED BY) e specificare una condizione secondo cui il suo valore verrà calcolato. Ad esempio, potremmo volere una colonna che calcoli il 10% dal valore del campo PRICE_1 nella nostra tabella. In questo caso, dovrebbe essere scritto il seguente comando:
CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));
Ma non pensare che ora, appena inseriamo i dati nel campo PRICE_1, nel campo PRICE_10 ci sarà una decima parte del valore di questo campo. No, il processo qui è più complicato. In realtà, otterremo la decima parte richiesta solo riferendoci al campo PRICE_10, ad esempio quando si esegue una query SELECT su questa tabella. Cioè, nessun dato viene memorizzato in un campo calcolato, e viene eseguito il calcolo dell’espressione collegata a un campo, e il risultato viene prodotto come risposta alla query.
Quindi, abbiamo considerato 3 modi principali per specificare i campi nella tabella. Ora consideriamo in dettaglio le opzioni che possono essere impostate quando si crea una colonna. L’opzione [DEFAULT {literal | NULL | USER}] - permette di impostare un valore di colonna per impostazione predefinita. È molto comodo per il riempimento automatico dei dati. Ci sono 3 modi per impostare un valore predefinito. Il primo è designato come literal e permette di impostare valori predefiniti come costanti di testo, numeri o date. Ad esempio, possiamo generare le seguenti espressioni per creare una colonna con valori predefiniti di testo: NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’
Quindi, tutti i campi inseriti nella tabella assumeranno i valori predefiniti, cioè se l’altro valore non è stato definito per il campo NAME, apparirà la stringa ‘Vasily Stanislavovich’. Il secondo modo per impostare un valore predefinito è specificare DEFAULT NULL nella definizione di una colonna. E nei record creati di nuovo il valore di questa colonna sarà NULL, se l’altro valore, certamente, non è stato impostato esplicitamente. Un esempio:
PRICE_1 DOUBLE PRECISION DEFAULT NULL
Il terzo modo per impostare un valore predefinito è specificare DEFAULT USER nella definizione di una colonna. Quindi nei record creati di nuovo questo campo conterrà il nome dell’utente corrente, cioè l’utente che ha effettuato una connessione con InterBase e ha eseguito questo inserimento (per maggiori dettagli sugli utenti vedi il capitolo “Sicurezza in InterBase: utenti, loro funzioni e diritti” (parte 4)). Per alcuni campi è essenziale che il campo abbia un valore non vuoto. Ad esempio, un campo che non può essere vuoto secondo la specifica del problema. Per impostare un vincolo a livello di database che un campo dovrebbe avere un valore definito, è necessario fare la seguente aggiunta alla descrizione di una colonna:
NAME VARCHAR(80) NOT NULL
Quindi, ci sarà un campo in cui non possono essere memorizzati valori nulli. Di solito il vincolo NOT NULL è combinato con l’opzione DEFAULT che assegna definitivamente un valore corretto a questo campo. Ma spesso il vincolo NOT NULL non è sufficiente. Ad esempio, nel caso di memorizzazione dei prezzi in un database è abbastanza chiaro che non possono assumere valori negativi (anche se sarebbe fantastico se fossimo pagati extra quando acquistiamo beni). Per far sì che il server controlli i valori dei prezzi inseriti in un database su una condizione di positività, è necessario definire una colonna nel seguente modo:
PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)
I valori inseriti nella colonna PRICE_1 saranno controllati su una condizione di positività. Va notato che diverse opzioni coerenti possono essere combinate, e ad esempio, possiamo impostare un valore non vuoto e un controllo di positività:
PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)
Quando si creano le colonne alcune opzioni non possono essere combinate, ad esempio è impossibile impostare NULL per impostazione predefinita e contemporaneamente il vincolo di valore non vuoto. Va segnalato che i controlli possono svolgere una serie di funzioni utili sulla gestione dei dati in un database. Considereremo il loro utilizzo in dettaglio nel capitolo “Vincoli del database”.
Quindi, abbiamo considerato i modi di creare tabelle e campi con diverse opzioni. Tuttavia, ci sono casi in cui dobbiamo modificare la tabella che già esiste. Certamente, possiamo ricreare la tabella completamente. Prima, dovremmo eseguire il comando di eliminazione della tabella e poi crearla di nuovo. Ad esempio:
DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);
Ma tale modo di modificare le tabelle ha notevoli svantaggi. Quando si elimina una tabella usando il comando DROP tutti i dati che la tabella contiene vengono eliminati e per non perderli, è necessario copiarli in tabelle temporanee. È piuttosto problematico. Pertanto, esiste il comando ALTER TABLE per una facile modifica della struttura delle tabelle che permette di aggiungere nuovi campi, eliminare quelli esistenti, così come aggiungere/eliminare vincoli di integrità referenziale.
Ad esempio, vogliamo aggiungere un’altra colonna alla tabella destinata a memorizzare i dati sul patronimico di una persona:
ALTER TABLE Table_example ADD Patronimic VARCHAR(80);
Dopo l’esecuzione di questo comando la nostra tabella Table_example avrà una nuova colonna con nome Patronimic e tipo VARCHAR (80). Se vogliamo eliminare una colonna con nome NAME dalla tabella, dovremmo eseguire quanto segue:
ALTER TABLE Table_example DROP Name;
Puoi vedere la sintassi completa dell’istruzione ALTER TABLE in [1.. È un comando molto utile, e lo useremo spesso.
E cosa fare, chiederai, se è necessario modificare una colonna? Ad esempio, abbiamo deciso che per memorizzare i nomi è meglio usare il campo HUMAN_NAME piuttosto che NAME. In questo caso possiamo applicare ALTER TABLE
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Se abbiamo deciso di modificare il tipo di un campo, ad esempio per aumentare il numero di caratteri memorizzati in un campo, dovremo cambiare il dominio di questo campo usando l’istruzione ALTER DOMAIN (vedi il capitolo “Tipi di dati” sopra).
Quindi, abbiamo considerato la creazione e la modifica delle tabelle in InterBase. Ora è il momento di approfondire un po’ la teoria dei database. InterBase, come è già stato detto, è un database relazionale. Inoltre, ciò significa che ogni record nella tabella dovrebbe avere una caratteristica secondo cui un record può essere distinto da un altro. Il meccanismo speciale delle chiavi uniche serve a questo scopo.
Chiavi primarie nelle tabelle
Certamente, possiamo creare una tabella che non contiene alcuna chiave. Non ci è proibito farlo. Ma, come è stato detto prima, la creazione di un database efficiente è impossibile senza osservare le regole di normalizzazione. La presenza delle chiavi è l’elemento più importante della normalizzazione. Pertanto, anche se non miriamo a considerare la teoria e la normalizzazione dei database, dovremmo introdurre una definizione delle chiavi e rivedere la loro funzione in InterBase. Andremo passo dopo passo e inizieremo con il tipo di chiave più comune - la chiave primaria.
Quindi, cos’è una chiave primaria? È uno o più campi nella tabella che identificano in modo univoco i record all’interno di questa tabella. Sembra difficile, ma in realtà tutto è molto semplice. Immagina una tabella ordinaria, ad esempio il foglio contabile. Qual è la prima colonna? Esatto, il numero di serie - 1, 2, 3 … Questo numero denota una riga unica all’interno della tabella, ed è sufficiente conoscere questo numero per trovare una stringa in questa tabella. In questo esempio, sarà una chiave primaria. La stragrande maggioranza delle tabelle in un database relazionale ha necessariamente una chiave primaria (PK - abbreviazione di Primary key). La linea guida comune quando si creano le tabelle è creare una chiave primaria. Una chiave primaria può essere creata quando si crea una tabella o successivamente. Supponiamo che al momento della creazione di una tabella abbiamo deciso che il campo ID sarà la nostra chiave primaria. Quindi possiamo aggiungere una chiave primaria nel seguente modo:
CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));
Cosa è stato fatto per creare una chiave primaria per la tabella table_example? Cerchiamo di vedere cosa è variato nella definizione della tabella. Innanzitutto, la colonna ID ha ricevuto una definizione aggiuntiva NOT NULL. Questo è importante, perché la chiave primaria deve essere unica e non deve contenere valori indefiniti. E NULL, come sai, è un valore indefinito. Pertanto, tutti i campi inclusi in una chiave primaria devono avere il vincolo NOT NULL. Per completare la creazione di una chiave primaria, alla fine della tabella si deve scrivere: CONSTRAINT ()
Puoi trovare la sintassi completa dei vincoli nel capitolo “Vincoli del database” þ. 1, e per il nostro esempio di chiave primaria, sarà:
CONSTRAINT pkTable PRIMARY KEY (ID)
Qui pkTable è il nome della chiave primaria, e ID sono le colonne che contiene. Questo modo di definire le chiavi primarie per le tabelle è comodo quando si creano molte tabelle (ad esempio, quando si costruisce un prototipo di database sulla base di script ottenuti da diversi strumenti CASE). Ma cosa fare se dobbiamo aggiungere/eliminare una chiave primaria a una tabella che esiste già ed è piena di dati? Per questo scopo, si deve applicare un’altra estensione del comando - ALTER TABLE. Un esempio di aggiunta di una chiave primaria alla nostra tabella:
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);
Pertanto, la tabella Table_example avrà esattamente la stessa chiave primaria dell’esempio precedente, quando è stata creata insieme alla tabella. Per eliminare una chiave primaria, si deve inserire il seguente comando:
ALTER TABLE Table_example DROP CONSTRAINT pkTable;
Così, la chiave con il nome pkTable verrà rimossa dal database.
Generatori - migliori amici delle chiavi primarie
Dobbiamo dire alcune parole sull’implementazione di una chiave primaria. Poiché è destinata a garantire l’unicità, nessun due record in una tabella possono avere gli stessi valori di questa chiave. Cioè, per soddisfare la condizione, quando si inserisce un nuovo record nella tabella, InterBase deve controllare tutti i record nella tabella e verificare se la tabella contiene tali valori o no. Per una ricerca rapida, InterBase ha un meccanismo di indici - oggetti speciali di InterBase che permettono di trovare un record nella tabella molto rapidamente. Pertanto, quando si crea o si elimina la chiave primaria, viene creato o eliminato un indice per quel campo (o quei campi) incluso nella chiave primaria.
Come detto in precedenza, la chiave primaria può contenere più campi. Così, possiamo notare l’unicità di una combinazione di valori di questi campi. Ad esempio, se definiamo una chiave per i campi ID e NAME, il server controllerà che non ci siano combinazioni identiche di questi campi nella tabella. Cioè, le combinazioni di campi ID e 1 e “Ivanov”, 2 e “Ivanov” saranno corrette perché differiscono nei valori del campo ID.
Pertanto, la chiave primaria può includere più campi di qualsiasi tipo. Tuttavia, in pratica il tipo di chiave più comune è il contatore - un campo intero che contiene valori crescenti. Perché è così? È il riflesso di una vecchia disputa tra chiavi naturali e sostitutive. Il concetto di chiavi naturali dice che come chiave dovremmo cercare di usare i valori realmente esistenti nel dominio di dati che il database riflette. Ad esempio, se sviluppiamo un sistema di registrazione delle persone per un ufficio passaporti, secondo questo concetto una combinazione di numero e serie del passaporto dovrebbe essere presa come chiave primaria. Davvero, ogni persona deve avere una combinazione unica di numero e serie del passaporto. Tuttavia, cosa fare con il fatto che una persona può cambiare il passaporto durante la sua vita (in relazione al raggiungimento di una certa età, al matrimonio, ecc.)? In questo caso, dovremo cambiare il numero e la serie del passaporto associati alla persona concreta, cioè, in realtà, cambiare la nostra chiave primaria. Questo è indesiderabile dal punto di vista dello sviluppo di applicazioni di database: considerando un sistema ramificato di comunicazione tra tabelle (il prossimo capitolo è dedicato a questo), lo sviluppatore dovrà fare grandi sforzi per controllare questa situazione.
Pertanto, nella maggior parte dei casi si usa una chiave sostitutiva. Sostitutiva - significa artificiale, cioè non esistente nel dominio di dati che il nostro database descrive, e creata artificialmente - per la comodità dello sviluppo di applicazioni di database. Come detto, di solito un contatore è una chiave primaria. Alcuni DBMS, come Paradox e MS SQL, hanno un tipo speciale - il contatore (auto increment). Quando si aggiunge un nuovo record alla tabella, il valore del campo aumenta automaticamente con questo tipo per il valore di un incremento - di solito per unità. In InterBase non esiste un campo di tipo contatore, tuttavia tale comportamento può essere realizzato. Per creare il campo che verrebbe riempito automaticamente quando si aggiunge un record alla tabella, si usa una raccolta di risorse: il primo di essi è il generatore.
Cos’è un generatore? Parlando in modo semplice, un generatore è un contatore nominato. All’interno di un database, possiamo creare un contatore, dargli un nome unico all’interno di questa base e controllare i valori di questo contatore. Sarà un generatore. Ecco un esempio di istruzioni DDL che te lo spiegherà:
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
Nella prima riga di questo esempio viene creato un generatore con nome g1, e nella seconda riga viene assegnato il valore 2445 a questo generatore. Ora c’è una domanda: come usare il generatore ottenuto. C’è una funzione incorporata GEN_ID in InterBase per ottenere e cambiare i valori dei generatori. Questa funzione prende come parametri il nome del generatore e il valore dell’incremento, che deve essere applicato al generatore dato, e restituisce il valore intero corrispondente al valore del generatore, ottenuto come risultato dell’aggiunta dell’incremento ad esso. Ecco un esempio di chiamata della funzione GEN_ID in un trigger o in una stored procedure:
Current_value = GEN_ID (g1, 1)
Se vogliamo ricevere il valore del generatore, possiamo usare la seguente query:
SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE
Poiché la tabella RDB $ Database contiene sempre un solo record, riceveremo un valore del generatore g1 come risultato della query data.
Qui current_value è una variabile (nei prossimi capitoli troverai informazioni su come usare le variabili in InterBase), g1 - un generatore, 1 - incremento. In questo esempio, il valore del generatore g1 verrà assegnato alla variabile current_value dopo aver aggiunto l’incremento 1 ad esso, cioè il valore successivo del generatore. Nota che l’incremento può non essere uguale a 1! Inoltre, può essere anche negativo: Current_value = GEN_ID (g1, -23)
Come risultato dell’esecuzione di questa funzione, il valore corrente del generatore g1 sarà meno 23. Come puoi vedere, la gamma di possibili applicazioni dei generatori è piuttosto ampia - può essere usato non solo per ottenere valori di chiavi primarie, ma anche per monitorare cambiamenti globali in un database.
Le persone familiari con i database potrebbero fare una domanda: “cosa succederà se contemporaneamente alcuni clienti cercheranno di inserire dati nella stessa tabella e allo stesso tempo “tireranno” i generatori? Riceveranno gli stessi o diversi valori del generatore?”. Riceveranno inequivocabilmente valori DIVERSI del generatore. Per quanto “simultaneo” sia stato il tentativo di ricevere il valore del generatore, chiunque abbia fatto richiesta riceverà un valore unico. È garantito dalla “costruzione” dei generatori: lavorano al livello più basso del server e nessun processo di registrazione o inserimento li influenza - spesso si dice che i generatori lavorano “fuori dal contesto delle transazioni”. Se vuoi sapere sulle transazioni, leggi il capitolo “Transazioni. Parametri delle transazioni” (parte 1); come sono organizzati i generatori - “Struttura del database InterBase” (parte 4). Bene, a nome dei generatori abbiamo un meccanismo affidabile per creare chiavi primarie uniche. Tuttavia, possiamo usare questo meccanismo? Come mettere il valore ricevuto dal generatore in un campo della chiave primaria?
Per questo scopo, ci sono due modi - inserire una chiave primaria per conto di un client e per conto di un server. Per padroneggiare il primo modo, dovremmo riferirci al capitolo “Uso dei componenti principali di FIBPlus” e per capire il secondo - al capitolo “Trigger” (parte 1). Qui esamineremo brevemente il punto principale di entrambi i modi.
Nel caso di creazione di una chiave primaria per conto di un client, accade quanto segue. Quando viene generato il record che sarà inserito in un database, viene eseguita la chiamata della funzione GEN_ID (, 1) e il valore ricevuto viene sostituito per questo record. Poi avviene l’inserimento nella tabella, e siamo garantiti di ricevere una chiave primaria unica.
Il secondo modo - creare una chiave primaria per conto di un server - in generale elimina qualsiasi preoccupazione da parte del client su quale valore avrà la chiave primaria. In questo caso, quando si inserisce il record, funziona il trigger - un oggetto speciale del database che può eseguire qualsiasi operazione durante l’inserimento/eliminazione/aggiornamento dei record nelle tabelle. E in questo trigger vengono eseguite le seguenti operazioni: chiamata della funzione GEN_ID, ricezione del valore richiesto del generatore e suo inserimento nella tabella. Il vantaggio del secondo modo è che quando si sviluppa un’applicazione client non c’è bisogno di preoccuparsi della creazione di una chiave primaria, l’unica cosa da fare è scrivere un trigger necessario una volta. Ma lo svantaggio è che non possiamo ricevere il valore della chiave generata nell’applicazione subito dopo l’inserimento! Se usiamo il primo modo, possiamo ricevere il valore della chiave primaria anche se dobbiamo preoccuparci della sua creazione ogni volta che inseriamo. È difficile dire con certezza quale modo sia migliore, tutto dipende dal problema specifico. Più avanti in questo libro considereremo possibili varianti per risolvere le questioni sul lavoro con la chiave primaria.
Conclusione
Quindi, in questo capitolo abbiamo esaminato come creare e aggiornare tabelle in InterBase, così come gestire le chiavi primarie. Abbiamo considerato i principali oggetti in InterBase che possono essere condizionatamente chiamati statici, perché memorizzano solo informazioni e non eseguono la loro conversione. Più avanti, parleremo dei modi di controllo delle informazioni e di conversione delle informazioni all’interno di un database.