Delphi - Táblák létrehozása

Transact SQL 9. rész

forráskód letöltése
Foglalkoztunk már az adatbázisok létrehozásával. Most azt vizsgáljuk, hogy a létrehozott adatbázisokba miként készíthetünk új táblákat. 01.sql
Kezdjük egy adatbázis létrehozásával, melyben elvégezhetjük a példa tábláink létrehozását. Az adatbázis több állományból fog állni, melyek két állomány csoportot alkotnak. A Primary csoportba kerül a TableTest01 és TableTest02, míg a Group1 nevű csoportba kerül a TableTest03 és TableTest04 állomány. Ennek majd ott lesz jelentősége mikor azt vizsgáljuk, hogy a tábla létrehozásánál miként írhatjuk elő, hogy az melyik állomány csoportba kerüljön. Mivel az adatbázis egyes állományait akár külön winchesteren is tárolhatnánk, így elérhetjük, hogy az egy adott winchesteren melyik tábla is kapjon helyet.

02.sql
Táblát létrehozni a CREATE TABLE utasítással tudunk. A létrehozandó tábla nevét a CREATE TABLE utasítás után adhatjuk meg. A legegyszerűbb eset, amikor csak az oszlopok nevét és típusát adjuk meg. Az oszlopokra vonatkozó információ zárójelek között, vesszővel elválasztva sorolható fel.
USE TableTest
CREATE TABLE Table01
(
   value1 int,
   value2 varchar(50),
   value3 nvarchar(50),
   value4 smallint,
   value5 datetime
)
A felhasználható adattípusok az alábbiak lehetnek:
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • decimal
  • numeric
  • money
  • smallmoney
  • float
  • real
  • datetime
  • smalldatetime
  • char
  • varchar
  • text
  • nchar
  • nvarchar
  • ntext
  • binary
  • varbinary
  • image
  • cursor
  • sql_variant
  • table
  • timestamp
  • uniqueidentifier

03.sql
Alapértelmezett érték rendelhető egy-egy oszlophoz. Ha egy új sor beszúrásakor egy olyan mező számára nem adunk értéket, melynél lett beállítva alapértelmezett érték, akkor ott automatikusan ez kerül felhasználásra. Alapértelmezett értéket a DEFAULT kulcsszó után adhatunk meg zárójelek között. A megadott érték lehet egy konstans, vagy egy rendszer változó, de akár egy függvény is.
USE TableTest
CREATE TABLE Table02
(
   value1 int DEFAULT (100),
   value2 varchar(50) DEFAULT ('szöveg'),
   value3 nvarchar(50) DEFAULT (SYSTEM_USER),
   value4 nvarchar(50) DEFAULT (SESSION_USER),
   value5 smallint,
   value6 datetime DEFAULT (getdate())
)

04.sql
Lehetőségünk van arra, hogy a mező értéke automatikusan növekedjen az előzőhöz képest egy megadott értékkel. Ehhez az IDENTITY kulcsszó megadására van szükség, mely után zárójelbe írhatunk két paramétert. Az első a kezdő sorszámot, míg a második a növekményt adja meg.
Elsődleges kulcs kijelölését a PRIMARY KEY kulcsszavak használatával érhetjük el. Ebből táblánként csak egy lehet.
Ha szeretnénk garantálni, hogy egy oszlop adatai között ne lehessen két egyforma, akkor az UNIQUE kulcsszó megadásával ezt is elérhetjük.
Ha nem elfogadható, hogy egy oszlop értékei között a null szerepeljen, akkor ennek tiltását a NOT NULL kulcsszóval megtehetjük. Ha ekkor egy új sor kerül beszúrásra és az adott oszlop nem kap értéket, akkor hiba keletkezik.
A bevezetőben már említettük, hogy előírható az is, hogy egy-egy tábla melyik állománycsoportba kerüljön. Ezt megtehetjük a tábla definíciót lezáró zárójel után az ON kulcsszó és a kívánt állománycsoport nevének megadásával.
USE TableTest
CREATE TABLE Table03
(
   value1 int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
   value2 int UNIQUE,
   value3 varchar(30) NOT NULL
)
ON Group1

05.sql
Már rögtön a tábla létrehozásánál gondoskodhatunk arról, hogy az adott oszlopba ne kerülhessen be bármilyen adat. Ezt úgy érhetjük el, hogy a CHECK kulcsszó után zárójelek között megadunk egy logikai kifejezést, melyet kiértékelve, ha hamis lesz az eredmény, akkor az adatok tárolása nem történik meg.
USE TableTest
CREATE TABLE Table04
(
   value1 int CHECK (value1>100),
   value2 int CHECK (value2 in (10, 20, 30))
)

06.sql
Kalkulált mezők létrehozására is van lehetőségünk. Ezt kihasználva előállíthatunk olyan adatokat, melyek egy vagy több másik mező adatát felhasználva jönnek létre. Ezekbe a mezőkbe adatot nem írhatunk, mivel azok értékét a megadott kifejezés fogja minden esetben meghatározni. Kifejezést az oszlop neve után adhatunk meg az AS kulcsszó után. A kifejezés egy tetszőleges művelet lehet, melynek eredménye az adott oszlop értéke lesz.
Az ilyen oszlopok esetében azok típusát sem kell megadni, mivel ez a kifejezés kiértékelése után mindig egyértelmű.
USE TableTest
CREATE TABLE Table05
(
   value1 varchar(50),
   value2 varchar(50),
   value3 as value1 + value2,
   value4 float,
   value5 float,
   value6 as (value4 * value5) / 100
)

07.sql
Az alábbiakban egy olyan táblát hozunk létre, ahol a value1 oszlop egy globálisan egyedi azonosítót (GUID) fog tartalmazni. Ennek értékét majd a NewID függvény szolgáltatja, így a tábla feltöltésekor nincs rá gondunk.
USE TableTest
CREATE TABLE Table06
(
  value1 uniqueidentifier ROWGUIDCOL DEFAULT (NEWID()),
  value2 varchar(5)
)

08.sql
Adatbázisok létrehozásánál már láthattuk, hogy miként tudjuk előírni, hogy milyen kódlap kerüljön felhasználásra az adatok rendezése esetén. Lehetőségünk van azonban arra is, hogy ezt az előírást oszlophoz rendeljük, így akár mindegyik oszlopunkban más módszer szerint rendezhetünk. Ehhez a COLLATE kulcsszó után kell megadnunk a rendezési elv megnevezését. Ennek listáját megkaphatjuk a SELECT * FROM ::fn_helpcollations() lekérdezés futtatásával.
USE TableTest
CREATE TABLE Table07
(
   value1 varchar(10) COLLATE Hungarian_CI_AI,
   value2 varchar(10) COLLATE Hungarian_CS_AI
)

09.sql
Az előző példában létrehozott táblára futtatunk most egy egyszerű lekérdezést, ahol a value1 oszlop szerinti rendezést kérjük. Megfigyelhető, hogy a rendezés most nem lesz érzékeny a nagy és kisbetűkre.
select value1 from Table07 order by value1



10.sql
A 8. példában létrehozott táblára futtatunk most egy egyszerű lekérdezést, ahol a value2 oszlop szerinti rendezést kérjük. Megfigyelhető, hogy a rendezés most érzékeny lesz a nagy és kisbetűk közötti különbségre.
select value2 from Table07 order by value2

11.sql
Létrehozunk most egy egyszerű táblát, benne egy elsődleges kulccsal. Erre a táblára a következő példában lesz szükségünk.
USE TableTest
CREATE TABLE Table08A
(
   masterid  int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   value1 varchar(10)
)

12.sql
Az előzőleg létrehozott táblához most létrehozunk egy újat, melyet összekötünk vele a masterid mező alapján. Az összekötéshez a REFERENCES kulcsszót adjuk meg, majd a tábla nevét, amellyel az összekötést végezzük el. Ezt követően zárójelben megadható a hivatkozott tábla azon mezőjének neve, melyen keresztül az összekötést elvégezzük. Az ON DELETE CASCADE záradék segítségével elérjük, hogy ha a Table08A táblából törlésre kerülnek sorok, akkor a Table08B-ből is törölve lesznek azok a sorok, ahol a masterid mezők értéke megegyező.
USE TableTest
CREATE TABLE Table08B
(
   masterid int
     NOT NULL
     REFERENCES Table08A(masterid) ON DELETE CASCADE,
   value1 int
)

13.sql
Tesztképpen most töröljük a Table08A-ból azt a sort, ahol a masterid 1. Ennek következménye az kell hogy legyen, hogy a Table08B-ből is eltávolításra kerül az a két sor, melyet a tábla létrehozásakor szúrtunk be master = 1 értékkel. A törlés után futtatott select tehát már nem listázhatja ki ezeket a sorokat.
USE TableTest
DELETE FROM Table08A WHERE (masterid = 1)
select * from Table08B

14.sql
Példánk befejeztével töröljük a létrehozott példaadatbázist.
use Master
DROP DATABASE TableTest
select 'Adatbázis törölve'


Transact SQL cikksorozat