Таблице. Примарни кључеви и генератори
NOTICE: Овај документ је поглавље из књиге „The InterBase World" коју су написали Алексеј Ковазин и Серг Востриков.
InterBase је релациони систем за управљање базама података (DBMS). То такође значи да су сви подаци у InterBase-у смештени као табеле. Табела, како је реализована са SQL тачке гледишта, веома је слична обичној табели коју можете нацртати ручно на папиру или направити у програму као што је Microsoft Excel. Табеле у InterBase-у имају колоне и редове где се смештају подаци. Табела нужно има име, јединствено у оквиру једне базе података. Табеле су главно складиште информација у бази података и стога морате бити веома пажљиви приликом креирања табела.
Постоје правила која описују како креирати табеле у релационој бази података, одражавајући податке из стварног света и истовремено омогућавајући ефикасно складиштење информација у бази. Процес примене ових правила за дизајнирање „исправне" базе назива се нормализација. Намерно смо ставили под знаке навода реч „исправна", јер „нормализована база" и „оптимизована база" нису синоними. Не морате се недвосмислено придржавати правила нормализације - увек примените корекцију за спецификацију датог проблема.
Нормализација табела у бази података детаљно је размотрена у књизи [14. и стога нећемо покушавати да обухватимо необухватљиво и вратићемо се нашој теми разговора - InterBase табелама. Размотримо синтаксу DDL (DDL - Data Definition Language, за више детаља погледајте глосар) наредбе која омогућава креирање табела:
CREATE TABLE table [EXTERNAL [FILE] “”] ( [, | …]);
Овде је table име креиране табеле, - опис колона (понекад ћемо рећи - поља) креиране табеле. Опција table [EXTERNAL [FILE] “”] значи да ће бити креирана такозвана екстерна табела која се не чува у заједничкој датотеци базе, већ у посебној датотеци са именом . Као што видите, све је једноставно - дефинишемо име табеле и колоне које садржи. Сада ћемо детаљно размотрити како дефинисати колоне. Синтакса креирања колоне описана је следећом DDL наредбом:
= col { datatype | COMPUTED [BY] (< expr>) | domain}
[DEFAULT { literal | NULL | USER}]
[NOT NULL] [ ]
[COLLATE collation]
Ово је прилично велика дефиниција, међутим у дефиницији колоне само је мали део датих наредби обавезан. Свака колона у табели мора имати име, јединствено у оквиру табеле, као и тип података дефинисан наредбом datatype, или израз за израчунавање вредности колоне (за израчунате колоне), или домен (погледајте доле), дефинисан domain. Типови података размотрени су у поглављу „Типови података"; стога лако можете разумети како се формира SQL-израз за креирање табеле.
Повежимо се са нашом базом FIRSTBASE.gdb креираном раније у поглављу „Креирање базе", и покушаћемо да радимо са табелама у пракси. Када је реч о креирању, брисању и ажурирању табела, било који од InterBase административних алата - од оних наведених у апликацији „InterBase алати за администраторе и програмере", као и стандардни услужни програм isql.exe из сета испоруке било које InterBase клон верзије, биће одговарајући.
Ево примера једноставне табеле под називом TABLE_EXAMPLE која садржи 3 поља различитих типова:
CREATE TABLE Table_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Ова табела илуструје најчешћи случај у процесу развоја базе података. Међутим, постоје и други методи дефинисања поља. На пример, можемо поставити тип поља користећи домене. Домен је тип који дефинише корисник ради погодности примене одређених комбинација параметара типа. На пример, могуће је дефинисати домен D_ID за спецификацију поља идентификатора. Након што дефинишемо домен, можемо га користити за постављање типа поља:
CREATE DOMAIN D_ID AS INTEGER; CREATE TABLE Тable_example ( ID D_ID, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION);
Поље ID ће имати тип дефинисан доменом D_ID. Дакле, након што дефинишемо тип поља у домену, потребне провере и ограничења, можемо применити овај домен више пута за креирање поља исте функције. На пример, монетарних, без заморног и опасног копирања дефиниција променљивих типова. Трећи начин да се постави колона у табели јесте да се дефинише као израчуната (COMPUTED BY) и да се наведе услов према којем ће се њена вредност израчунавати. На пример, можемо пожелети да у нашој табели имамо колону која израчунава 10 % од вредности поља PRICE_1. У овом случају, треба написати следећу команду:
CREATE TABLE Тable_example ( ID INTEGER, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, PRICE_10 COMPUTED BY (PRICE_1.0.1));
Али немојте мислити да ће се, чим унесемо податке у поље PRICE_1, у пољу PRICE_10 појавити десети део вредности тог поља. Не, процес је овде сложенији. Заправо, добићемо потребан десети део тек када се позовемо на поље PRICE_10, на пример приликом извршавања SELECT упита на ову табелу. То значи да се у израчунатом пољу не чувају подаци, већ се врши израчунавање израза повезаног са пољем, а резултат се производи као одговор на упит.
Дакле, размотрили смо 3 главна начина спецификације поља у табели. Сада ћемо детаљно размотрити опције које се могу поставити приликом креирања колоне. Опција [DEFAULT {literal | NULL | USER}] - омогућава постављање подразумеване вредности колоне. Веома је погодна за аутоматско попуњавање података. Постоје 3 начина за постављање подразумеване вредности. Први је означен као literal и омогућава постављање подразумеваних вредности као текст константи, бројева или датума. На пример, можемо генерисати следеће изразе за креирање колоне са текстуалним подразумеваним вредностима: NAME VARCHAR(80) DEFAULT ‘Василий Станиславович’
Дакле, сва поља уметнута у табелу ће добити подразумеване вредности, тј. ако друга вредност није дефинисана за поље NAME, појавиће се стринг ‘Vasily Stanislavovich’. Други начин постављања подразумеване вредности јесте навођење DEFAULT NULL у дефиницији колоне. И у новокреираним записима вредност ове колоне ће бити NULL, ако друга вредност, наравно, није експлицитно постављена. Пример:
PRICE_1 DOUBLE PRECISION DEFAULT NULL
Трећи начин постављања подразумеване вредности јесте навођење DEFAULT USER у дефиницији колоне. Дакле, у новокреираним записима ово поље ће садржати име тренутног корисника, тј. корисника који је успоставио везу са InterBase-ом и извршио ово уметање (за више детаља о корисницима погледајте поглавље „Безбедност у InterBase-у: корисници, њихове функције и права" (део 4)). За нека поља је од суштинског значаја да поље има непразну вредност. На пример, поље које према спецификацији проблема не може бити празно. Да би се поставило ограничење на нивоу базе да поље мора имати дефинисану вредност, потребно је додати следећи додатак опису колоне:
NAME VARCHAR(80) NOT NULL
Дакле, постојаће поље у које се не могу чувати NULL вредности. Обично се ограничење NOT NULL комбинује са опцијом DEFAULT која недвосмислено додељује исправну вредност овом пољу. Али често ограничење NOT NULL није довољно. На пример, у случају чувања цена у бази података сасвим је јасно да оне не могу имати негативне вредности (иако би било сјајно када бисмо били доплаћени при куповини робе). Да би сервер проверио вредности цена уметнутих у базу на услов позитивности, потребно је дефинисати колону на следећи начин:
PRICE_1 DOUBLE PRECISION CHECK (PRICE_1>0)
Вредности уметнуте у колону PRICE_1 биће проверене на услов позитивности. Треба напоменути да се различите конзистентне опције могу комбиновати, и на пример, можемо поставити непразну вредност и проверу позитивности:
PRICE_1 DOUBLE PRECISION NOT NULL CHECK (PRICE_1>0)
Када креирате колоне, неке опције се не могу комбиновати, на пример, немогуће је подразумевано поставити NULL и истовремено ограничити да вредност не буде празна. Треба напоменути да провере могу обављати скуп корисних функција за управљање подацима у бази података. Детаљно ћемо размотрити њихову употребу у поглављу „Ограничења базе података“.
Дакле, размотрили смо начине креирања табела и поља са различитим опцијама. Међутим, постоје случајеви када морамо да изменимо табелу која већ постоји. Наравно, можемо у потпуности поново креирати табелу. Прво, треба да извршимо команду за брисање табеле, а затим је поново креирамо. На пример:
DROP TABLE Table_example; CREATE TABLE Table_example(ID NUMERIC(15,2);
Али овакав начин измене табела има значајне недостатке. Када се табела брише помоћу DROP команде, сви подаци које табела садржи се бришу и да их не бисмо изгубили, потребно је копирати их у привремене табеле. То је прилично захтевно. Због тога постоји ALTER TABLE команда за једноставну измену структуре табела, која омогућава додавање нових поља, брисање постојећих, као и додавање/брисање ограничења референцијалног интегритета.
На пример, желимо да додамо још једну колону у табелу намењену за чување података о патронимику особе:
ALTER TABLE Table_example ADD Patronimic VARCHAR(80);
Након извршења ове команде, наша табела Table_example ће имати нову колону са именом Patronimic и типом VARCHAR(80). Ако желимо да избришемо колону са именом NAME из табеле, треба да извршимо следеће:
ALTER TABLE Table_example DROP Name;
Комплетну синтаксу ALTER TABLE изјаве можете видети у [1]. То је веома корисна команда и често ћемо је користити.
А шта радити, питаћете, ако је потребно изменити колону? На пример, одлучили смо да је за чување имена боље користити поље HUMAN_NAME него NAME. У овом случају можемо применити ALTER TABLE:
ALTER TABLE Table_example ALTER COLUMN NAME TO HUMAN_NAME;
Ако смо одлучили да изменимо тип поља, на пример да повећамо број карактера који се чувају у пољу, мораћемо да променимо домен овог поља користећи изјаву ALTER DOMAIN (погледајте поглавље „Типови података“ изнад).
Дакле, размотрили смо креирање и измену табела у InterBase-у. Сада је време да се мало дубље упустимо у теорију база података. InterBase је, као што је већ речено, релациона база података. То додатно значи да сваки запис у табели треба да има карактеристику по којој се један запис може разликовати од другог. Посебан механизам јединствених кључева служи овој сврси.
Примарни кључеви у табелама
Наравно, можемо креирати табелу која не садржи ниједан кључ. Није нам забрањено да то урадимо. Али, као што је раније речено, креирање ефикасне базе података није могуће без поштовања правила нормализације. Присуство кључева је најважнији елемент нормализације. Стога, иако не тежимо разматрању теорије и нормализације база података, треба да уведемо дефиницију кључева и размотримо њихову функцију у InterBase-у. Ићи ћемо корак по корак и почећемо са најчешћим типом кључа - примарним кључем.
Дакле, шта је примарни кључ? То је једно или више поља у табели која јединствено идентификују записе унутар те табеле. Звучи компликовано, али у ствари је све врло једноставно. Замислите обичну табелу, на пример рачуноводствени лист. Која је прва колона? Тачно, редни број - 1, 2, 3… Овај број означава јединствени ред унутар табеле и довољно је знати овај број да бисте пронашли ред у овој табели. У овом примеру, то ће бити примарни кључ. Огромна већина табела у релационој бази података нужно има примарни кључ (PK - скраћеница од Primary key). Уобичајена смерница при креирању табела је да се креира примарни кључ. Примарни кључ се може креирати приликом креирања табеле или касније. Претпоставимо да смо у тренутку креирања табеле одлучили да ће поље ID бити наш примарни кључ. Затим можемо додати примарни кључ на следећи начин:
CREATE TABLE Table_example ( ID INTEGER NOT NULL, NAME VARCHAR(80), PRICE_1 DOUBLE PRECISION, CONSTRAINT pkTable PRIMARY KEY (ID));
Шта је урађено да би се креирао примарни кључ за табелу table_example? Потражимо шта се променило у дефиницији табеле? Прво, колона ID је добила додатну дефиницију NOT NULL. То је важно, јер примарни кључ треба да буде јединствен и без недефинисаних вредности. А NULL је, као што знате, недефинисана вредност. Дакле, сва поља укључена у примарни кључ треба да имају ограничење NOT NULL. Да би се довршило креирање примарног кључа, на крају табеле треба написати: CONSTRAINT ()
Комплетну синтаксу ограничења можете наћи у поглављу „Ограничења базе података“ þ. 1, а за наш пример примарног кључа, то ће изгледати овако:
CONSTRAINT pkTable PRIMARY KEY (ID)
Овде је pkTable име примарног кључа, а ID су колоне које он садржи. Овакав начин дефинисања примарних кључева за табеле је згодан при масовном креирању табела (на пример, при изградњи прототипа базе података на основу скрипти добијених из различитих CASE-ресурса). Али шта радити ако треба да додамо/избришемо примарни кључ у табелу која већ постоји и попуњена је подацима? У ту сврху треба применити још једну екстензију команде - ALTER TABLE. Пример додавања примарног кључа нашој табели:
ALTER TABLE TABLE_EXAMPLE ADD CONSTRAINT FF PRIMARY KEY (ID);
Дакле, табела Table_example ће имати потпуно исти примарни кључ као у претходном примеру када је креиран заједно са табелом. Да бисте избрисали примарни кључ, треба унети следећу команду:
ALTER TABLE Table_example DROP CONSTRAINT pkTable;
Дакле, кључ са именом pkTable ће бити уклоњен из базе података.
Генератори - најбољи пријатељи примарних кључева
Морамо рећи неколико речи о имплементацији примарног кључа. Пошто је он намењен за одржавање јединствености, ниједна два записа у једној табели не могу имати исте вредности овог кључа. То значи да, да би се испунио услов, при уметању новог записа у табелу InterBase мора да провери све записе у табели и утврди да ли табела садржи такве вредности или не. За брзу претрагу, InterBase има механизам индекса - посебне InterBase објекте који омогућавају веома брзо проналажење записа у табели. Због тога се при креирању и брисању примарног кључа креира или брише индекс за то поље (или поља) које је укључено у примарни кључ.
Као што је раније речено, примарни кључ може садржати неколико поља. Тако можемо приметити јединственост комбинације вредности ових поља. На пример, ако дефинишемо кључ за поља ID и NAME, сервер ће контролисати да у табели не постоје идентичне комбинације ових поља. То јест, комбинације поља ID и 1 и „Ivanov“, 2 и „Ivanov“ ће бити исправне јер се разликују у вредностима поља ID.
Дакле, примарни кључ може укључивати неколико поља било ког типа. Међутим, у пракси најчешћи тип кључа је бројач - целобројно поље које садржи растуће вредности. Зашто је то тако? То је одраз старе расправе између природних и заменских кључева. Концепт природних кључева каже да као кључ треба да покушамо да користимо вредности које стварно постоје у домену података који база одражава. На пример, ако развијамо систем регистрације људи за пасошки уред, према овом концепту комбинација броја и серије пасоша треба да се узме као примарни кључ. Заиста, свака особа мора имати јединствену комбинацију броја и серије пасоша. Међутим, шта радити са чињеницом да особа може променити пасош током живота (због навршених година, склапања брака итд.)? У овом случају, мораћемо да променимо број и серију пасоша који су у вези са конкретном особом, односно, заправо, да променимо наш примарни кључ. То је непожељно са становишта развоја апликација за базе података: узимајући у обзир разгранати систем комуникација између табела (следеће поглавље је посвећено овоме), програмер ће морати да уложи велике напоре да контролише ову ситуацију.
Стога, замена кључ се користи у већини случајева. Замена - значи вештачки, тј. не постоји у домену података који наша база података описује, и вештачки је створен - ради погодности развоја апликација за базе података. Као што је речено, обично је бројач примарни кључ. Неке СУБП, као што су Paradox и MS SQL, имају посебан тип - бројач (ауто инкремент). Када се додаје нови запис у табелу, вредност поља се аутоматски повећава са овим типом за вредност инкремента - обично за јединице. У InterBase-у не постоји поље типа бројача, међутим такво понашање се може реализовати. За креирање поља, које би се аутоматски попуњавало при додавању записа у табелу, користи се скуп ресурса: први од њих је генератор.
Шта је генератор? Једноставно речено, генератор је именовани бројач. У оквиру базе података, можемо креирати бројач, дати му јединствено име у оквиру те базе и контролисати вредности тог бројача. То ће бити генератор. Ево примера DDL изјава које ће вам то објаснити:
CREATE GENERATOR g1; SET GENERATOR g1 TO 2445;
У првој линији овог примера креира се генератор са именом g1, а у другој линији вредност 2445 се додељује овом генератору. Сада се поставља питање како користити добијени генератор. Постоји уграђена функција GEN_ID у InterBase-у за добијање и мењање вредности генератора. Ова функција узима као параметре име генератора и вредност инкремента, који треба применити на дати генератор, и враћа целобројну вредност која одговара вредности генератора, добијеној као резултат додавања инкремента на њега. Ево примера позива функције GEN_ID у тригеру или ускладиштеној процедури:
Current_value = GEN_ID (g1, 1)
Ако желимо да примимо вредност генератора, можемо користити следећи упит:
SELECT GEN_ID(g1, 1)FROM RDB$ DATABASE
Пошто табела RDB $ Database увек садржи само један запис, добићемо вредност генератора g1 као резултат датог упита.
Овде је current_value променљива (у следећим поглављима наћи ћете информације како користити променљиве у InterBase-у), g1 - генератор, 1 - инкремент. У овом примеру, вредност генератора g1 ће доспети у променљиву current_value након додавања инкремента 1 на њу, тј. следећа вредност генератора. Обратите пажњу да инкремент не мора бити једнак 1! Штавише, може бити чак и негативан: Current_value = GEN_ID (g1, -23)
Као резултат извршавања ове функције, тренутна вредност генератора g1 ће бити минус 23. Као што видите, опсег могућих примена генератора је прилично широк - може се користити не само за добијање вредности примарних кључева, већ и за праћење глобалних промена у бази података.
Људи упознати са базама података могу поставити питање: “шта ће се десити ако истовремено неки клијенти покушају да ставе податке у исту табелу и истовремено “повуку” генераторе? Да ли ће добити исте или различите вредности генератора?”. Они ће недвосмислено добити РАЗЛИЧИТЕ вредности генератора. Колико год “истовремен” био покушај добијања вредности генератора, свако ко се пријави добиће јединствену вредност. То је гарантовано “конструкцијом” генератора: они раде на најнижем нивоу сервера и никакви процеси записивања и уметања не утичу на њих - често се каже да генератори раде “изван контекста трансакција”. Ако желите да сазнате о трансакцијама, прочитајте поглавље “Трансакције. Параметри трансакција” (део 1); како су генератори уређени - “Структура InterBase базе података” (део 4). Дакле, у име генератора имамо поуздан механизам за креирање јединствених примарних кључева. Међутим, можемо ли користити овај механизам? Како ставити вредност добијену од генератора у поље примарног кључа?
У ту сврху постоје два начина - уметнути примарни кључ у име клијента и у име сервера. Да бисте савладали први начин, треба да се обратите поглављу “Коришћење главних компоненти FIBPlus-а” и да разумете други - поглављу “Тригери” (део 1). Овде ћемо укратко размотрити главну суштину оба начина.
У случају креирања примарног кључа у име клијента, дешава се следеће. Када се генерише запис који ће бити уметнут у базу података, извршава се позив функције GEN_ID (, 1) и добијена вредност се замењује за овај запис. Затим се врши уметање у табелу, и гарантовано добијамо јединствени примарни кључ.
Други начин - креирање примарног кључа у име сервера - уопште елиминише било какву бригу са стране клијента о томе која ће бити вредност примарног кључа. У овом случају при уметању записа ради тригер - посебан објекат базе података, који може извршити било које операције при уметању/брисању/ажурирању записа у табелама. И у овом тригеру се извршавају следеће операције: позив функције GEN_ID, добијање потребне вредности генератора и њено уметање у табелу. Предност другог начина је што при развоју клијентске апликације нема потребе да се уопште бринете о креирању примарног кључа, једино што треба да урадите је да једном напишете потребан тригер. Али недостатак је што не можемо добити вредност генерисаног кључа у апликацији одмах након уметања! Ако користимо први начин, можемо добити вредност примарног кључа, иако треба да водимо рачуна о његовом креирању сваки пут при уметању. Тешко је рећи са сигурношћу који је начин бољи, све зависи од конкретног проблема. Даље у овој књизи размотрићемо могуће варијанте решавања питања о раду са примарним кључем.
Закључак
Дакле, у овом поглављу смо размотрили како креирати и ажурирати табеле у InterBase-у, као и управљати примарним кључевима. Тако смо размотрили главне објекте у InterBase-у који се условно могу назвати статичним, јер они само чувају информације и не врше њихову конверзију. Даље ћемо говорити о начинима контроле информација и конверзије информација у оквиру базе података.