Pagina personale di:
Carlo Vecchio
appunti di C#, R, SQL Server, ASP.NET, algoritmi, numeri
Vai ai contenuti

SQL Server - Tabelle

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’.



© 2020 Carlo Vecchio
Torna ai contenuti