SQL Server

Tabelle

Anche le tabelle possono essere create, modificate ed eliminate:

- Utilizzando SSMS (SQL Server Management Studio.

- Tramite query.

Creazione di una tabella

Tramite SSMS

In ‘Object Explorer’ click dx su ‘Tables’, poi click su ‘New Table…’.

Si apre una finestra sulla quale bisogna inserire i campi della tabella. Per ogni campo bisogna indicare il nome (Column Nome), il tipo (Data Type) e un flag che indica se il campo può assumere valori nulli (Allow Nulls).

Dopo aver inserito tutti i campi, fare click destro sul nome della scheda, dove c’è la scritta ‘dbo.Table_1’. Nel menu contestuale che compare, fare click su ‘Save Table_1’.

Infine, dare il nome voluto alla tabella.

Per impostare una colonna ‘Primary Key’, click destro sul nome del campo e nel menu contestuale che compare, click su ‘Set Primary Key’.

Per impostare una colonna ‘Identity’ (cioè auto incrementale: il campo è un numero intero generato dal database, tipicamente parte da 1 e – per ogni riga inserita – è incrementato di 1), selezionare il campo e impostare le proprietà ‘IsIdentity’, ‘IdentityIncrement’ e ‘Identity Seed’.

Tramite query

Il comando per creare una tabella è CREATE TABLE, al quale bisogna indicare tutti i campi della tabella con le relative proprietà.
Esempio:

CREATE TABLE dbo.Esempio

(

Id INT PRIMARY KEY IDENTITY(1, 1),

Campo1 NVARCHAR(30) NULL,

Campo2 NVARCHAR(30) NOT NULL,

Campo3 NVARCHAR(30) NOT NULL DEFAULT 'my_default',

Campo4 INT NULL,

Campo5 INT NOT NULL,

Campo6 INT NOT NULL DEFAULT 0

)

Come si vede, dopo il nome del campo bisogna indicare il tipo e opzionalmente altre proprietà. Se non ne sono indicate il campo è NULL.
Le proprietà principali sono:

- NULL: il campo può assumere il valore NULL.

- NOT NULL: il campo non può assumere il valore NULL.

- NOT NULL DEFAULT X: il campo non può assumere il valore NULL; se non impostato nella query di inserimento allora assume il valore indicato dopo la parola ‘DEFAULT’.

- PRIMARY KEY: il campo è la chiave primaria della tabella.

- IDENTITY(N, M): il campo è generato dal database a partire dal valore N e incrementato ogni volta del valore M.

Chiave esterna (Foreign Key)

Una chiave esterna in una tabella è un campo il cui valore è la chiave primaria di un’altra tabella. Si può pensare alla chiave esterna come a un ‘puntatore’ verso un’altra tabella.
In SQL Server si può aggiungere un vincolo (Constraint) che previene l’inserimento di valori non validi in una chiave esterna.
Nel caso si provi ad aggiungere un valore non valido, SQL Server restituisce l’errore 547.
Esempio.
Si abbiano le tabelle ‘tblPersone’ e ‘tblCittà’ così definite:

CREATE TABLE dbo.tblPersone

(

Id INT PRIMARY KEY IDENTITY(1, 1),

Nome NVARCHAR(30) NOT NULL,

Cognome NVARCHAR(30) NOT NULL,

IdCitta INT NULL

)

CREATE TABLE dbo.tblCittà

(

Id INT PRIMARY KEY IDENTITY(1, 1),

Citta NVARCHAR(30) NOT NULL

)

Nella tabella ‘tblCittà’ ci sono anche le seguenti tre righe:

Id Città

1 Milano

2 Roma

3 Firenze

Senza una chiave esterna si possono inserire righe nella tabella ‘tblPersone’ con il campo IdCitta qualsiasi, anche NULL (perché è stato definito NULL).
La INSERT seguente infatti non dà errore:

INSERT INTO tblPersone (Nome, Cognome, IdCitta)

VALUES

('Leonardo', 'Da Vinci', NULL),

('Raffaello', 'Sanzio', 5)

Se si definisce un vincolo di chiave esterna, la INSERT con il campo IdCitta = NULL, procede senza errore, mentre la INSERT con il IdCitta = 5 dà l’errore 547. Infatti non esiste una riga nella tabella tblCittà con Id = 5.
Per definire la chiave esterna la sintassi è la seguente:

ALTER TABLE tblPersone ADD CONSTRAINT tblPersone_IdCitta_FK

FOREIGN KEY (IdCitta) REFERENCES tblCittà(Id)

In generale, definendo ‘TabellaFK’ la tabella dove si aggiunge la chiave esterna sul campo ‘CampoFK’ e ‘TabellaKEY’ la tabella con le chiavi sul campo ‘CampoKEY’:

ALTER TABLE TabellaFK ADD CONSTRAINT TabellaFK_CampoFK_FK

FOREIGN KEY (CampoFK) REFERENCES TabellaKEY (CampoKEY)

Il nome del vincolo è a scelta e di solito viene definito con il nome della TabellaFK, il campo della TabellaFK e la stringa ‘FK’ separati da ‘_’.
Per eliminare un Contraint:

ALTER TABLE tblPersone

DROP CONSTRAINT tblPersone_IdCitta_FK

Le chiavi esterne di una tabella sono visibili in SSMS nell’Object Explorer, database -> tabella -> Keys.

Integrità referenziale

Una volta inserita nella tabella ‘tblPersone’ la chiave esterna sul campo ‘IdCItta’, non è più possibile inserire un record con un Id non presente nella tabella ‘tblCittà’ (mentre è possibile inserire record con ‘IdCitta’ = null). Se si prova si ottiene l’errore 547. E allo stesso modo non è possibile neanche fare un Update di un record, impostanto un valore di una chiave esterna non presente nella tabella ‘tblCittà’.
Inoltre non è possibile cancellare o aggiornare record dalla tabella ‘tblCittà’ il cui Id è presente nella tabella ‘tblPersone’: questo fa sì che i record siano consistenti ed è chiamato ‘Integrità referenziale’ (cioè i riferimenti tra le tabelle rimangono integri). Se si prova a cancellare o modificare un record che violi l’integrità referenziale, si ottiene sempre l’errore 547.
Ci sono diversi modi per gestire l’integrità referenziale e mantenere integri i riferimenti:

- No Action: è il Default, viene generato l’errore 547.

- Cascade: permette l’eliminazione della riga e cancella ‘a cascata’ anche le righe della tabella che hanno questa chiave esterna.

- Set Null: permette l’eliminazione della riga e imposta a NULL la chiave esterna.

- Set Default: permette l’eliminazione della riga e imposta al valore di Default la chiave esterna.

Per definire in un Constraint una integrità referenziale diversa da quella di Default, si utilizza la sintassi mostrata con l’esempio seguente.

ALTER TABLE tblPersone ADD CONSTRAINT tblPersone_IdCitta_FK

FOREIGN KEY (IdCitta) REFERENCES tblCittà(Id)

ON DELETE SET NULL

ON UPDATE SET NULL

Default Constraint

Un default constraint in un campo di una tabella è il valore che il campo assume qualora non sia indicato in una insert. Il valore NULL è accettato nella insert anche se è presente un valore di default diverso da NULL.
Per definire un valore di default in una tabella già esistente la sintassi è la seguente:

ALTER TABLE tblPersone ADD CONSTRAINT DF_Cognome

DEFAULT 'Sconosciuto' FOR Cognome

Sintassi generale:

ALTER TABLE Tabella ADD CONSTRAINT DF_Campo

DEFAULT 'Sconosciuto' FOR Campo

Il nome del vincolo è a scelta e di solito viene definito con ‘DF’ e il nome del campo separati da ‘_’.
I vincoli di default di una tabella sono visibili in SSMS nell’Object Explorer, database -> tabella -> Constraints.

Check Constraint

Un check constraint in un campo di una tabella è un vincolo che limita il valore che il campo può assumere. Il valore NULL è comunque accettato.
Per definire un check constraint in una tabella già esistente la sintassi è la seguente:

ALTER TABLE MyTable

ADD CONSTRAINT CK_MyTable_Età CHECK (Età > 0 AND Età <= 120)

Sintassi generale:

ALTER TABLE Tabella

ADD CONSTRAINT CK_Tabella_Campo CHECK (Condizione booleana)

I check constraint di una tabella sono visibili in SSMS nell’Object Explorer, database -> tabella -> Constraints.

Identity column

Le Identity Column, sono campi della tabella il cui valore è generato automaticamente dal database. Per chi utilizza il database MySql, queste colonne si chiamano ‘AutoIncrement’.
Per definire una Identity Column, nello script di creazione tabella occorre inserire IDENTITY(N, M) dove N indica il numero di partenza (default è 1) e M è l’incremento (default è 1).
Esempio:

CREATE TABLE MyTable

(

Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

... ...

)

Si consideri la seguente tabella:

CREATE TABLE MyTable

(

Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

Nome nvarchar(50) NULL,

Cognome nvarchar(50) NULL,

Età int NULL

)

In una SQL di inserimento, i valori di questi campi non vanno indicati; se si prova, il database dà l’errore 544. Per esempio la seguente query di Insert, dà appunto l’errore:

INSERT INTO MyTable

(Id, Nome, Cognome, Età)

VALUES

(1, 'Leonardo', 'Da Vinci', 50)

Mentre la seguente query di Insert, funziona:

INSERT INTO MyTable

(Nome, Cognome, Età)

VALUES

('Leonardo', 'Da Vinci', 50)

Analogamente anche una query di Update su una colonna Identity non è permessa; se si prova il database dà l’errore 8102.
In alcune situazioni, può essere utile forzare un certo valore in un campo definito Identity, cioè poter eseguire una query di Insert indicando anche il campo definito Identity. Per fare questo occorre:

1. Dare il comando:

SET IDENTITY_INSERT MyTable ON

2. Eseguire la INSERT imposando anche un valore per il campo Identity.

3. Dare il comando per ripristinare la colonna come Identity.

SET IDENTITY_INSERT MyTable OFF

Le successive query di Insert con l’Id generato dal database, riprendono dal massimo valore nella tabella incrementato di 1.
Se invece si vuole azzerare il valore inserito dal database nella colonna Identity (o lo si vuole impostare a un valore a piacere), utilizzare il comando seguente. Esso è utile per esempio nel caso si eliminino tutti i record della tabella e si vuole ricominciare a inserire il campo Identity, da 1 (o da un altro valore, modificando il terzo parametro della ‘CheckIdent’).

DBCC CHECKIDENT('MyTable', RESEED, 0)

Può essere utile leggere l’ultimo valore Identity utilizzato; per farlo si possono utilizzare le seguenti istruzioni:

SCOPE_IDENTITY()

Valida per la stessa sessione e lo stesso ‘scope’.

@@IDENTITY

Valida per la stessa sessione e per tutti gli ‘scope’.

IDENT_CURRENT('MyTable')

Valida per tutte le sessioni e per tutti gli gli ‘scope’.