Ограничения базы данных в Firebird и InterBase
НАПОМЕНА: Овај документ је поглавље из књиге „The InterBase World“ коју су написали Алексеј Ковазин и Серг Востриков.
Ово поглавље је посвећено ограничењима у InterBase и Firebird базама података. Ограничења базе података су правила која дефинишу међусобне односе између таблица и могу проверавати и мењати податке у бази. Ова правила су реализована као посебни објекти базе података. Главна предност коришћења ограничења састоји се у могућности имплементације провере података и дела пословне логике апликације на нивоу базе података, тј. да се она централизује и поједностави, чиме се развој апликација са базама података чини лакшим и поузданијим.
Почетници у развоју често занемарују коришћење ограничења базе података, сматрајући да она ометају креативан рад. Међутим, заправо такво мишљење је формирано на недовољном знању теорије и праксе дизајнирања база података.
Истовремено, нај искуснији дизајнери се усуђују да одбију коришћење неких врста ограничења, због чега њихове апликације добијају на брзини. Искуство стручних дизајнера им омогућава да веома добро разумеју рад сервера и прецизно предвиде његово понашање у сложеним случајевима, па је боље да почетници у InterBase програмирању не опонашају такве поступке искусних колега.
У оквиру ове књиге не разматрамо дизајн базе података, па за више информација о овом питању погледајте списак литературе на крају књиге. Овде ћемо само прегледати све врсте ограничења у InterBase бази података и размотрити примере њихове примене.
Врсте ограничења у бази података
Постоје следеће врсте ограничења у InterBase бази података:
- PRIMARY KEY;
- UNIQUE KEY;
- FOREIGN KEY
- могу укључити аутоматске тригере - ON UPDATE и ON DELETE;
- CHECK
У претходним поглављима поменули смо нека од ових ограничења, јер је то било неопходно за логичко представљање градива, али сада ћемо детаљније размотрити њихову синтаксу, примену и имплементацију. Ограничења базе података су два типа - заснована на једном пољу и заснована на више поља таблице. Синтакса оба типа ограничења је дата у наставку.
= [CONSTRAINT constraint]
[ …]
= {UNIQUE | PRIMARY KEY
| CHECK ( )
| REFERENCES other_table [( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}
Синтакса ограничења заснованих на више поља је следећа:
= [CONSTRAINT constraint]
[< tconstraint> …]
= {{PRIMARY KEY | UNIQUE} ( col [, col …])
| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK ( )}
Разлика у синтакси између ограничења заснованих на једном и на више поља је очигледна - у последњем случају можемо навести неколико поља укључених у ограничење. У случају ограничења заснованог на једном пољу, све описане опције односе се само на текуће поље. Свакако, ова два типа ограничења имају различит начин примене: ограничењима заснована на једном пољу једноставно се додају дефиницији потребног поља, а ограничењима заснована на више поља наводе се након зареза у општој дефиницији таблице. Детаљни примери дати су у следећим деловима овог поглавља.
Пример типичног ограничења
Заправо, ограничењима заснована на једном пољу су посебан случај ограничења заснованих на више поља.
Пример креирања примарног кључа коришћењем ова два различита приступа дат је у наставку. Направимо таблицу која садржи само једно поље и поставимо јој ограничење примарног кључа.
Ево примера примарног кључа коришћењем синтаксе ограничења заснованог на једном пољу:
CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); У овом примеру, примарни кључ са именом pktest креиран је за поље ID_PK. Као резултат, имамо прилично компактан опис у једном реду. Можемо користити синтаксу ограничења заснованих на више поља за исту сврху: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));
Креирање ограничења
Размотримо детаљније креирање ограничења. Прво у опису опште синтаксе ограничења долази [CONSTRAINT constraint] опција. Као што видите, ова опција је постављена у угласте заграде, тј. није обавезна.
Коришћењем ове опције можете поставити име креираном ограничењу, како у случају примене синтаксе ограничења заснованих на једном пољу, тако и у случају ограничења заснованих на више поља. Ако нисте навели име за ограничење, InterBase ће га аутоматски генерисати. Ипак, боље је поставити име креираном ограничењу ради побољшања читљивости шеме базе података и поједностављења управљања ограничењима касније.
Након што сте поставили име ограничењу, треба дефинисати његов тип. Размотримо различите врсте ограничења редом како су наведене у опису опште синтаксе ограничења.
Примарни и јединствени кључеви
Примарни кључеви су једна од главних врста ограничења базе података. Они се примењују за једнозначну идентификацију записа у таблици. Претпоставимо да чувамо листу људи у бази података. Сасвим је могуће да ће постојати две (или више) особе са истим презименом, именом и патронимиком. Како можемо разликовати једну особу од друге (наравно, питање је разликовања једне особе од друге према информацијама сачуваним у бази података)?
У овом случају, „особа“ је представљена једним записом у таблици, па можемо поставити општије питање - како можемо разликовати један запис у (било којој) таблици од другог записа у истој таблици. У ту сврху користе се ограничењима - примарни кључеви. Примарни кључ представља једно или неколико поља у таблици, чија је комбинација јединствена за сваки запис. Нема понављајућих вредности примарног кључа за једну таблицу.
Јединствени кључеви обављају исту функцију - они такође служе за једнозначну идентификацију записа у таблици. Разлика између примарних и јединствених кључева је у томе што у таблици може постојати само један примарни кључ, а што се тиче јединствених кључева - може их бити неколико. Примећено је да и примарни и јединствени кључ могу бити коришћени као референтна основа за стране кључеве (види даље).
Формални опис појмова примарних и јединствених кључева, као и других важних дефиниција, може се наћи у додатку „Речник“ на крају књиге. Синтакса креирања примарног и јединственог кључа заснованог на јединственом пољу је следећа:
< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}
Примери примарних и јединствених кључева:
CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*примарни кључ*/
uk1 VARCHAR(50) NOT NULL UNIQUE,/*јединствени кључ */
uk2 INTEGER NOT NULL UNIQUE /\* још један јединствени кључ */);
Синтакса креирања примарних и јединствених кључева заснованих на више поља:
= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Оваква синтакса омогућава креирање кључева на основу комбинације поља. Ево примера креирања примарних и јединствених кључева од више поља:
CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*примарни кључ pkt заснован на два поља*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*јединствени кључ ukt1 заснован на два поља*/
Обратите пажњу да сва поља укључена у примарне и јединствене кључеве треба да буду декларисана као NOT NULL јер ови кључеви не могу имати недефинисану вредност. Поред креирања ограничењима примарних и јединствених кључева приликом креирања таблице, постоји могућност додавања ограничењима таблици која већ постоји. У овом случају користи се DDL: ALTER TABLE наредба. Синтакса додавања ограничењима примарног или јединственог кључа постојећој таблици је слична горе описаној:
ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Размотримо пример креирања примарног и јединственог кључа помоћу ALTER TABLE:
CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));
Затим додајемо кључеве. Прво примарни:
ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);
Затим јединствени: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);
Треба напоменути да само власник ове табеле или системски администратор SYSDBA (за више детаља о власницима и SYSDBA кориснику, погледајте поглавље „Сигурност у InterBase-у: корисници, њихове функције и права“ - део 4) може да изврши додавање (као и брисање) примарних и јединствених кључева у табелу.
Страни кључеви
Следеће ограничење које се често користи у InterBase базама података јесте ограничавање страним кључем. Ово је веома моћан алат за обезбеђивање референцијалног интегритета у бази података, који омогућава не само надгледање присуства исправних референци у бази, већ и аутоматско контролисање тих референци!
Суштина креирања страног кључа је следећа: ако две табеле служе за чување међусобно повезаних информација, потребно је гарантовати да ће та повезаност увек бити исправна. На пример, документ „отпремница“ који садржи општи заглавље (датум, број отпремнице, итд.) и скуп детаљних записа (опис робе, количина, итд.).
За чување таквог документа у бази података креирају се две табеле - једна за чување заглавља отпремница, а друга - за чување садржаја отпремнице - записа о роби и њиховој количини. Такве табеле називају се главна и подређена или табела-мастер и табела-детал.
Према здравом разуму, садржај отпремнице не може постојати без присуства њеног заглавља. Другим речима, не можемо уметнути запис о роби ако нисмо креирали заглавље отпремнице, и не можемо обрисати запис заглавља ако постоје записи о роби. За реализацију таквог понашања, табела заглавља и табела детаља повезују се помоћу ограничавања страним кључем.
Размотримо смисао постављања ограничавања страним кључем на примеру табела које садрже информације о отпремницама. У ту сврху, креираћемо две табеле за чување отпремнице - TITLE табелу за чување заглавља и INVENTORY табелу за чување информација о роби укљученој у отпремницу.
CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));
Обратите пажњу да смо одмах дефинисали примарни кључ у табели заглавља на основу ID_TITLE поља. Остала поља TITLE табеле садрже тривијалне информације о заглављу отпремнице - датум, број, коментар.
Сада дефинишемо табелу за чување информација о роби укљученој у отпремницу:
CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);
Погледајмо која поља су укључена у INVENTORY табелу. Прво, то је ID_INVENTORY - примарни кључ ове табеле. Затим следи целобројно поље FK_TITLE које служи као референца на идентификатор ID_TITLE заглавља у табели заглавља отпремница. Затим следе ProductName, Kolvo и Positio поља која описују опис робе, њену количину и позицију у отпремници. FK_TITLE поље је најважније за наш пример. Ако желимо да прикажемо информације о роби одређене отпремнице, требало би да користимо следећи упит, у којем mas_ID_TITLE параметар дефинише идентификатор заглавља:
SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE
У ствари, у описаној ситуацији ништа не спречава пуњење INVENTORY табеле записима који се односе на непостојеће записе у TITLE табели. Поред тога, ништа не омета брисање заглавља већ постојеће отпремнице, због чега записи о роби могу постати „без власника“. Сервер неће забранити извршавање свих ових уметања и брисања. Дакле, контрола над интегритетом података у бази потпуно је препуштена клијентској апликацији. Међутим, знате да неколико апликација, развијених можда од стране различитих програмера, може радити са једном базом података, што може довести до различитог тумачења података и грешака. Стога је неопходно поставити експлицитно ограничавање да се само они записи о роби који имају исправну референцу на заглавље отпремнице могу унети у INVENTORY табелу. То је, у ствари, ограничавање страним кључем које омогућава уметање само оних вредности, које се налазе у другој табели, у поља укључена у ограничавања.
Такво ограничавање може се креирати помоћу страног кључа. За дати пример, морамо поставити ограничавање страним кључем за FK_TITLE поље и повезати га са ID_TITLE примарним кључем у TITLE. Можемо додати страни кључ већ постојећој табели следећом командом:
ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)
Често при додавању страног кључа појављује се грешка - објекат је у употреби. Ствар је у томе што за креирање страног кључа морамо отворити базу података у ексклузивном режиму - да у исто време не буде других корисника. Такође не треба да се позивамо на измењену табелу - то може изазвати грешку објекат је у употреби.
Овде INVENTORY је назив табеле за коју се поставља ограничавање страним кључем; fktitle1 је назив страног кључа; FK_TITLE - поља која чине страни кључ; TITLE је назив табеле која даје вредности (основу референце) за страни кључ; ID_TITLE - поља примарног или јединственог кључа у TITLE табели, која служе као основа референце за страни кључ. Комплетна синтакса ограничавања страним кључем (са могућношћу креирања ограничавања на основу више поља) дата је испод:
= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Као што видите, дефиниције садрже велики скуп опција. За почетак, размотримо основну дефиницију страног кључа, која се најчешће користи у стварним базама података, а затим ћемо анализирати могуће опције.
Декларативни облик ограничавања страним кључем најчешће се користи када је одређен скуп поља (col [, col …]), која ће чинити ограничавање; као и other_table која садржи листу могућих вредности за страни кључ у пољима [(other_col [, other_col …]).
Ево примера такве дефиниције при креирању табеле:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);
Обратите пажњу да су у овој дефиницији кључне речи FOREIGN KEY изостављене, као и да се подразумева да ће се једино поље FK_TABLE користити као страни кључ. Комплетнији облик креирања страног кључа истовремено са табелом дат је у следећем примеру:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);
Коришћење NULL у пољима страног кључа
У пољима, на основу којих је креиран страни кључ, дозвољено је примењивати NULL-поља. Ова могућност је додата ради омогућавања међусобних референци. На пример, ако постоје две табеле које се међусобно позивају помоћу страних кључева. Ако не дозволимо празну референцу (тј. за NULL) у овим страним кључевима, биће немогуће додати било који запис у повезане табеле: да би се додао запис у прву табелу, потребно је имати запис у другој табели, и обрнуто.
Коришћење NULL као празне референце омогућава креирање међусобних референци двеју унакрсно повезаних табела, као и чување хијерархијских структура у релационим табелама - при чему се коренски чворови односе на „празне“ записе (тј. једноставно садрже NULL).
Проширене могућности подршке интегритета референци помоћу страног кључа
Обично је декларативна варијанта ограничења страног кључа сасвим довољна, сервер само пази да ће бити немогуће уметнути неисправне вредности у табелу са страним кључем или - при покушају тога, појављује се грешка. Али InterBase омогућава извршавање скупа аутоматских операција приликом измене / брисања страног кључа. У ту сврху користи се следећи скуп опција страног кључа:
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Ове опције омогућавају дефинисање различитих операција приликом ажурирања или брисања вредности страног кључа.
На пример, можемо поставити да приликом брисања примарног кључа у табели-мастеру, сви записи са истим страним кључем у подређеној табели буду обрисани. У овом случају, морамо дефинисати страни кључ на следећи начин:
ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE
Заправо, за имплементацију ових операција постоји системски окидач који извршава одређене операције. У табели 1.2 дат је опис операција различитих опција (обратите пажњу да опције NO ACTION|CASCADE|SET DEFAULT|SET NULL не могу бити коришћене у једној реченици ON XXX).
Табела 1.2
| Догађај | Операција | |||
| NO ACTION | CASCADE | SET DEFAULT | SET NULL | |
| ON DELETE | Приликом брисања страног кључа не ради ништа - користи се подразумевано | Приликом брисања брише све повезане записе из подређене табеле | Приликом измене поставља поље страног кључа на подразумевану вредност |
Приликом измене поставља поље страног кључа на NULL |
| ON UPDATE | Приликом измене не ради ништа - користи се подразумевано | Приликом измене записа мења све повезане записе у подређеној табели | Приликом брисања поставља поље страног кључа на подразумевану вредност |
Приликом брисања поставља поље страног кључа на NULL |
Ако не наведемо ништа или наведемо NO ACTION, морамо сами водити рачуна о промени страног кључа (у случају промене примарног), а приликом брисања примарног кључа треба претходно обрисати записе из подређене табеле. Будите веома пажљиви када користите CASCADE опцију: њено немарно коришћење може довести до брисања великог броја повезаних записа.
CHECK ограничење
Једно од најкориснијих ограничења у бази података је CHECK ограничење. Његова функција је врло једноставна - да провери вредност уметнуту у табелу према било ком услову и у складу са испуњењем тог услова, да уметне податке или не. Његова синтакса је прилично једноставна:
= [CONSTRAINT constraint] CHECK ( )}
Овде је constraint име ограничења; је услов претраге, у коме уметнута / ажурирана вредност може бити параметар. Ако је услов претраге испуњен, дозвољено је уметнути / ажурирати ову вредност, ако није - појављује се грешка. Најједноставнији пример CHECK-а:
create table checktst( ID integer CHECK(ID>0));
Овај CHECK одређује да ли је уметнута / ажурирана вредност поља ID већа од нуле, и у зависности од резултата дозвољава уметање / ажурирање нове вредности или обавештава о грешци (погледајте поглавље “Проширене могућности језика InterBase процедура” (део 1)).
Постоје и сложеније варијанте CHECK-ова. Комплетна синтакса услова претраге је следећа:
= {
{ | ()}
| [NOT] BETWEEN AND
| [NOT] LIKE [ESCAPE ]
| [NOT] IN ( [ , …] | )
| IS [NOT] NULL
| {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} ()
| EXISTS ( )
| SINGULAR ( )
| [NOT] CONTAINING
| [NOT] STARTING [WITH]
| ()
| NOT
| OR
| AND }
Дакле, CHECK пружа велики скуп опција за проверу уметнутих / ажурираних вредности. Треба запамтити следећа ограничења када користите CHECK:
- Подаци за CHECK се узимају само из текућег записа. Не би требало узимати податке за израз у CHECK-у из других записа исте табеле - они могу бити измењени од стране других корисника
- Поље може имати само једно CHECK ограничење
- Ако се за дефиницију поља користи домен који има CHECK доменско ограничење, оно не може бити редефинисано на нивоу конкретног поља у табели. Мора се рећи да су CHECK-ови имплементирани системским окидачима, стога морамо бити опрезнији када користимо веома дугачке услове, који могу значајно успорити процесе уметања и ажурирања записа.
Брисање ограничења
Врло често бришемо различита ограничења из најразличитијих разлога. Да би обрисали ограничење, треба користити ALTER TABLE изјаву следећег облика: ALTER TABLE tablename DROP CONSTRAINT constraintname
constraintname је име ограничења које треба обрисати. Ако је одређено име наведено приликом креирања ограничења, треба га користити, али ако није, морамо отворити било који InterBase административни алат, претражити сва ограничења повезана са њим и сазнати које системско име је InterBase генерисао за тражено ограничење.
Треба напоменути да само власник табеле или системски администратор SYSDBA може обрисати ограничења.